Concatenating in TI

[conflax]

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

[declanr]

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...

[conflax]

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

[asutcliffe]

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.

[declanr]

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.

[conflax]

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