Page 1 of 1
Passing Data field name as Parameter in TI
Posted: Wed Nov 18, 2009 11:28 pm
by ExApplix
My ODBC data contains data in different columns e.g:
Jan_Sales Feb_Sales Mar_Sales
100 500 650
I want to pass the DB Field name as a Parameter in my TI Process so that I don't have to write many processes for each month or to run the same process by making changes in each time.
Can I create a parameter pMonth=Jan_Sales and then use it within the CellPutN function?
something like CellPutN(pMonth,'myCube',Dim1, Dim2, Dim3,'sales');
Please help!
Re: Passing Data field name as Parameter in TI
Posted: Thu Nov 19, 2009 2:44 am
by LoadzaGrunt
For a source table that contains many months worth of data, you could have something similar to this in the Data tab of your TI:
Code: Select all
CellPutN(Jan_Sales,'YourCube',Dim1,Dim2,Dim3,'Sales');
CellPutN(Feb_Sales,'YourCube',Dim1,Dim2,Dim3,'Sales');
CellPutN(Mar_Sales,'YourCube',Dim1,Dim2,Dim3,'Sales');
...
Re: Passing Data field name as Parameter in TI
Posted: Thu Nov 19, 2009 6:04 pm
by ExApplix
I actually want to load the data on monthly basis, that is why I don't want to add multiple CellPutN() in my Data tab.
Is there any other suggestion or something which I can do using the Parameters?
Re: Passing Data field name as Parameter in TI
Posted: Fri Nov 20, 2009 12:31 pm
by Steve Rowe
The best I can suggest is to use an if statement
If (pMonth@='Jan');
valueToLoad=col1;
ElseIf (pMonth@='Feb');
valueToLoad=col2;
etc
Else (pMonth@='Dec');
valueToLoad=col12;
End;
CellPutN (valueToLoad, Cube ,etc);
I don't there is way to say use a parameter to declare a column as the one you interested in.
HTH
Steve
Re: Passing Data field name as Parameter in TI
Posted: Fri Nov 20, 2009 5:47 pm
by Wim Gielis
I tried many many variations of the EXPAND function, but could not make it work...
Re: Passing Data field name as Parameter in TI
Posted: Tue Nov 24, 2009 12:14 pm
by Steve Vincent
I have
Code: Select all
MonthN = 1;
VarN = 11;
While ( MonthN <= 228 );
MonthS = 'Month ' | NumberToString ( MonthN );
VarS = EXPAND ( '%V' | NumberToString ( VarN ) | '%' );
Each column of data in my source from V11 to V239 represents a different month. I have an attribute that denotes month 1 = jan 2009, Month 2 = Feb 2009 etc so i can relate the columns in the data source to the attributes. This means when the date range of the file changes, all we need to do is ensure the control attributes reflect the same range before we import the data.
What the code above does is equate the variable name (starting at V11) to a month number from the control cube (eg. V11 = Month 1 = Jan 2009). The CellPut statement uses "ATTRS ( ' Month Number ', MonthS , ' Demand Month ' )" to know what month i'm loading to, and the EXPAND line allows me to grab the VALUE from the relevant column. It means for each line of data in my source file, i actually load 228 datapoints. The normal way TI works is to load just one datapoint per line.
The person who wrote the original TI didn't know how to do this and so wrote the same CellPut statement 228 times
I needed to edit it and find / replace wouldn't have dealt with the needed change, so rather than go through all that manually i did some research to find a far better way of writing it
