TM1 Forum

Hi there,

Can anyone help me on how to concatenate two columns in a CSV file into one dimension in a TM1 cube using TI please?

I have CSV file columns "Century" containing value 20 and "V3" (replacement of Year reserved word) containing values 10, 11, 12. I want to be able to combine these into a dimension "Year" already containing 2010, 2011 and 2012.

I have read some of the posts in this forum suggesting code that needs to be entered into TI, but I am very new to TM1 and cannot work out the syntax or where this is supposed to go.

Currently "Year" is undefined as a variable and I have simply typed " Year = (Century | V3); " into the Metadata section of the TI advanced tab but this doesn't work. The process appears to run in correctly (at least I get the message "Process completed successfully") but I don't get any data.

What am I doing wrong?

Thanks
Steve

I'm pretty sure that Year is reserved and can;t be used???

Try sYear = Century | v3 ;

and also make sure that Century and V3 are defined as string.


For debugging purposes you can also export the sYear column as Ascii from within the TI...

Hi,

Thanks for this - however I am still not getting a result.

I have created a dimension sYear with 2010, 2011, 2012 elements included.

Century and V3 are both defined as type String, and also I have set them to contents "Other".
(previously tried setting as "Element" and linking both to sYear but receive message "does not allow duplicates").

I have tried entering the following statement in both the Prolog and Metadata sections, above the BEGIN-END statements, I have simply typed:-

sYear = Century | V3;

In both cases the cube is created but sYear does not exist as a dimension, although TI does bring in data.
All the other dimensions already exist and I am running a Create Cube process and deleting the cube each time.

Any thoughts? I am new to this (think trying to drive a car for the first time by reading the manual) so could just be doing something wrong that's really basic.

Thanks
Steve

conflax wrote:
I have tried entering the following statement in both the Prolog and Metadata sections, above the BEGIN-END statements, I have simply typed:-

sYear = Century | V3;

In both cases the cube is created but sYear does not exist as a dimension, although TI does bring in data.
All the other dimensions already exist and I am running a Create Cube process and deleting the cube each time.

What are you doing with the variable sYear after you've set it?

It sounds like you're using the wizard to create a cube and all it's dimensions. In which case, you'll want to create a new process variable on the variables tab and then map that a dimension. The code you're using to concatenate the two strings is right and can be used as the formula for your variable.

Sorry I think I may have misinterpreted your question originally.

When I said you should call it sYear instead of Year, I was just meaning the variable.

I get the impression that you believe writing sYear = Century | v3; should create the elements within a dimension sYear, am I correct?

What you actually need to do is have:

Prolog Tab
.............................................................

sDim = 'Year';

If ( DImensionExists ( sDim ) =0 );
DimensionCreate ( sDim );
DimensionElementInsert ( sDim, '', 'All Years', 'C');
EndIf;


..............................................................
MetaData Tab
..............................................................
sYear = Century | v3;

DimensionElementInsert ( 'Year', '', sYear, 'N');

and Possibly something along the lines of:

DimensionElementComponentAdd ( 'Year', 'All Years', sYear, 1);



EDIT - My post assumed you are not using the Wizard, In the instance that you are using the wizard... ignore me and go with the suggestions in the above post instead.

It's as simple as that!

Yes I was literally just typing sYear = Century | V3 into the Metadata area and yes I am using the wizard. Running a test version of TM1 without having had a vast amount of training, especially in TI.

Anyway, thanks for the advice it's sorted now.


Cheers,
Steve