Exporting the data through TI process

[Cognostm1.ibm]

Hi All,

I am exporting the data from View through TI process but i need the data in the below format.

Challenge1:I need to export the data for each scenario as separate export file. By default it is exporting all scenario elements.
Challenge 2: how can i display both Year and Month in same export file coming from same time dimension.

Sample Format:
Productcode;Year;Entity;Account;ProductType;Business;months;salesmeasure;Scenario;value
61755;CD;2011;A;OTHER;XYZ;Apr;PQR;version1;1

I am using ASCIIOUTPUT function for exporting the data.

Thanks,
shan

[Alan Kirk]

I am exporting the data from View through TI process but i need the data in the below format.

Challenge1:I need to export the data for each scenario as separate export file. By default it is exporting all scenario elements.

I gave you the link to the If() statement in the documentation in the last post of yours that I replied to. You can use an If() / ElseIf() block to do that. Use the Scenario variable as the comparison argument, and write to different output files via AsciiOutput depending on the value of the Scenario variable.

Alternatively incorporate the Scenario into the first argument to the AsciiOutput statement (the file name) which will ensure that each file has a different name depending on the scenario that the data row relates to.

Ensure that you read through the TM1 Reference Guide's "Rules Functions" section, specifically the "Text Rules Functions" group, as well as Syntax For Formulas in the Developers' Guide. That will give you what you need to be able to dynamically create the string that you need to feed to AsciiOutput as the file name.

Challenge 2: how can i display both Year and Month in same export file coming from same time dimension.

Sample Format:
Productcode;Year;Entity;Account;ProductType;Business;months;salesmeasure;Scenario;value
61755;CD;2011;A;OTHER;XYZ;Apr;PQR;version1;1

Please clarify. In the sample that you've provided you appear to have both a year and month time dimension, not a single time dimension.

[Cognostm1.ibm]

Let me try.

Thank you Alan

[Cognostm1.ibm]

Alan,

The output i need to generate should contain Year and Month. And both are from same dimension.

[Alan Kirk]

The output i need to generate should contain Year and Month. And both are from same dimension.

So what you're saying is that you need to split up month and year so that they appear in separate fields as you have in your sample data?

If the elements are in the correct format you can do that with the string manipulation functions that I referred to above. If you supply details of your dimension (what the names of the leaf elements are, the names of the consolidations and any aliases) and whether you're exporting at N or consolidated level I can be more specific.

[Cognostm1.ibm]

Alan,

Please find the screenshot as an attachment.
Year at Consolidation level and Months in leaf level.

[Alan Kirk]

Alan,

Please find the screenshot as an attachment.
Year at Consolidation level and Months in leaf level.

That makes it easy.

Your variable will probably come in as whatever the name of the dimension is. Each variable value will be a string like '2011.01'. In the Data tab you can just use the SubSt function to get the year:

Code: Select all

s_Yr = SUBST(VariableName, 1, 4);

and since you already have an attribute for the Month you can just look that up from an AttrS function:

Code: Select all

s_Mth = ATTRS('DimensionName', VariableName, 'Month_Abbr');

(Edit: For those looking at this thread in the future the OP did have a screenshot, since deleted, showing that the dimension had a Month_Abbr attribute which had text in the format Jan, Feb, etc. This isn't something native to TM1 but was added in their model.)


After that you can export it as

Code: Select all

AsciiOutput (FileName, Productcode,s_Yr, Entity, Account, ProductType, Business, s_Mth, salesmeasure, Scenario, NumberToString(Value));

[Cognostm1.ibm]

Alan,

Thank you So much!!!