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!
Can TI refer to previous row's data during import?
-
- Regular Participant
- Posts: 226
- Joined: Thu Apr 02, 2009 2:51 pm
- OLAP Product: IBM Planning Analytics
- Version: Latest version
- Excel Version: 2003 to 2019
Can TI refer to previous row's data during import?
Planning Analytics latest version, including Cloud
-
- MVP
- Posts: 3654
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Can TI refer to previous row's data during import?
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.
Let's say your named variable is vStore. Put this at the top of your data tab.
Code: Select all
IF( Trim(vStore) @<> '' );
vModStore = vStore;
EndIF;
- Steve Vincent
- Site Admin
- Posts: 1054
- Joined: Mon May 12, 2008 8:33 am
- OLAP Product: TM1
- Version: 10.2.2 FP1
- Excel Version: 2010
- Location: UK
Re: Can TI refer to previous row's data during import?
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.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
-
- Regular Participant
- Posts: 226
- Joined: Thu Apr 02, 2009 2:51 pm
- OLAP Product: IBM Planning Analytics
- Version: Latest version
- Excel Version: 2003 to 2019
Re: Can TI refer to previous row's data during import?
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?
just curious, can i add vModStore in the Variable tab instead of writing the script in Data tab?
Planning Analytics latest version, including Cloud
-
- Community Contributor
- Posts: 132
- Joined: Thu Oct 15, 2009 7:45 pm
- OLAP Product: TM1
- Version: 9.4.1 9.5 9.5.1
- Excel Version: 2003 2007
Re: Can TI refer to previous row's data during import?
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.
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.
Ankur Jain
- Steve Vincent
- Site Admin
- Posts: 1054
- Joined: Mon May 12, 2008 8:33 am
- OLAP Product: TM1
- Version: 10.2.2 FP1
- Excel Version: 2010
- Location: UK
Re: Can TI refer to previous row's data during import?
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.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet