TM1 Forum

Hi all,

I received a source CSV file that reads something like this. Let's just say it contains only 2 rows:

Row 1: Store ABC, Sales Qty, Month1 Qty, Month2 Qty
Row 2: <blank>, Sales Amt, Month1 Amt, Month2 Amt

I'm trying to import Sales Qty and Amt for Store ABC into a cube. however, the application that generates the CSV file does not repeat the Store ID (Store ABC) for every row.

If it possible for TI, while processing Row 2 in Data tab, make reference to the same variable "Store" in the previous row? I'm thinking of creating a new variable that says something like "if vStore variable is blank, lookup vStore variable in the previous row". Hence, if one store has 5 rows of records, the next 4 rows after the first row with Store ID and be have something in the new variable.

Please advise.

Thanks!

Easy.

Let's say your named variable is vStore. Put this at the top of your data tab. vModStore will only change when you have a new non-blank value in your vStore variable. If vStore is blank then vModStore will retail the last value. Then just refer in all your CellPutN and any other functions to vModStore not vStore.

Nice solution As long as you can trust the data source to be consistant, it will work perfectly. Just be wary that if some dodgy / different data comes you way you'll need to bare that in mind.

wow! Thanks to both of you! i will probably do a vModStore = Trim(vStore) just to make sure the variable is recognisable.

just curious, can i add vModStore in the Variable tab instead of writing the script in Data tab?

You have 2 options:
1) add the code that lotsaram posted to the beginning of the data tab.
2) create a new variable in the Variables tab with that code. It will then place the code for you in the generated statements section of the data tab. This will require you to assure that your cellputn function is below the generated statements section.

Option 1 is probably simpler.

and option 2 in effect does automatically what option 1 will do manually anyway. all the variable tab does is create the code in the relevant tab for you, but you can do either it makes little difference.