TM1 TI Process with messy data source csv

[BigG]

Hi all, just a data source processing question for TM1 TI

I build a dimension in TM1 TI off a .csv which has a table followed by a second table on same worksheet. I do not want to touch or continue processing off the second table so I want metadata (dim structure build) and data (attributes) to stop when the end of table 1 is reached. This is represented by a text statement in column A and final row saying 'end of dimension data', I have considered processbreak but the help says this will jump me to epilog when all I really want to do is move from metadata to data to epilog everytime the last record is met.

Option is to tidy up the source csv but its automated and pretty rigid in design...so would pefer to find a way in TM1

Thanks in advance...

G

[rmackenzie]

In the Prolog tab, somewhere:

Code: Select all

nSkipData=0;

In the Metadata tab at the top:

Code: Select all

IF(V1@='end of dimension data');
  nSkipData=1;
ENDIF;

IF(nSkipData=0);
  # your processing logic goes here
ELSE;
  # reset the flag and
  # skip all the other rows in the csv
  nSkipData=0;
  ItemSkip;
ENDIF;

In the Data tab, do a similar thing, but now you can ProcessBreak:

Code: Select all

IF(V1@='end of dimension data');
  nSkipData=1;
ENDIF;

IF(nSkipData=0);
  # your processing logic goes here
ELSE;
  # just jump to Epilog
  ProcessBreak;
ENDIF;

I think that's what you want, unless I totally misread the question!

[BigG]

thanks for quick response, looks like what I want, will try this out in morning and tell you how it goes... I assume the itemskip in metadata will just take me straight to data tab, not go to next row in data source and continue with metadata processing ( next rows do not have 'end of dimension data' in column A x row)?

cheers

[Alan Kirk]

thanks for quick response, looks like what I want, will try this out in morning and tell you how it goes... I assume the itemskip in metadata will just take me straight to data tab, not go to next row in data source and continue with metadata processing ( next rows do not have 'end of dimension data' in column A x row)?

No, an Itemskip will only skip that particular row of data; it won't move you off the tab. Unfortunately that's where there's a small problem with RMackenzie's solution, but it's an easily fixed one and the basic idea is sound.

For all of the lines before the 'end of dimension data' one, the value of the nSkipData variable will be 0 (since that's what it was set to in the Prolog).

As soon as you reach that row, the value is set to 1.

When you reach the IF/ELSE block, the IF won't be true, and the ELSE will be executed. That block of code will skip that row.

Unfortunately it will also reset the value of nSkipData to 0.

That means that when the following row is processed on the metadata tab:
- The IF(V1@='end of dimension data'); row won't be true, so
- nSkipData will remain 0, so
- The IF block rather than the Else block will be executed.

The easy way around this is just to omit the nSkipData=0; in the Metadata tab. In that way the value will remain as 1 all the way through the remaining rows of the Metadata tab, meaning that an ItemSkip will be executed on all of those rows. In the Data tab, when you hit the 'End of dimension data" line you can call ProcessBreak.

The other way would be to execute the metadata and data code in two separate processes which would mean that you could use ProcessBreak in both and wouldn't have to itemskip as many rows, but if the number of rows isn't huge it's probably not worth the effort.

[rmackenzie]

Unfortunately that's where there's a small problem with RMackenzie's solution, but it's an easily fixed one and the basic idea is sound.

Thanks Alan, good pickup... another way is to have two flags - one for the data tab, one for the metadata tab - to stop the processing once the 'exit' row is reached.

In the Prolog tab, somewhere:

Code: Select all

nSkipRowsInMetadataTab=0;
nSkipRowsInDataTab=0;

In the Metadata tab at the top:

Code: Select all

IF(V1@='end of dimension data');
  nSkipRowsInMetadataTab=1;
ENDIF;

IF(nSkipRowsInMetadataTab=0);
  # your processing logic goes here
ELSE;
  # skip all the other rows in the csv
  ItemSkip;
ENDIF;

In the Data tab, do a similar thing, but now you can ProcessBreak:

Code: Select all

IF(V1@='end of dimension data');
  nSkipRowsInDataTab=1;
ENDIF;

IF(nSkipRowsInDataTab=0);
  # your processing logic goes here
ELSE;
  # just jump to Epilog
  ProcessBreak;
ENDIF;

Hope that helps,
Robin

[BigG]

thanks guys, cut and paste of Robin's last piece of code did the trick...magic...too easy