Identifying column names in datasource with variables

Post Reply
vasek1192
Posts: 47
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Version: 2.0.9.3
Excel Version: 2019

Identifying column names in datasource with variables

Post by vasek1192 »

Hi, I wondered if it is possible to identify column names in datasource with varible through which i could loop?

I am uploading data from csv file. Rows - Accounts, Columns - Months (viz. picture)
DataSource.png
DataSource.png (59.62 KiB) Viewed 4861 times
I cannot change the structure of the Data Source. I need to upload this data on the cube with time dimension T_Cas_M which has elements 01, 02, 03... with attributes corresponding to the column names: JAN, FEB, MAR... Through which i loop.

The variable sMonthCode holds this value(name of the month) for each cycle of the loop.

Code: Select all

sStartMonth = CellGetS(sVerzeCube, psVerze, sVorMesic);
sStopMonth = '12';

nStartMonth = StringToNumber(sStartMonth);
nStopMonth = StringToNumber(sStopMonth);

WHILE(nStartMonth <= nStopMonth);
		sStartMonth = NumberToString(nStartMonth);
    	IF(LONG(sStartMonth)=1);
    		sStartMonth = '0'|numberToString(nStartMonth); 
        ELSE;
        	sStartMonth = numberToString(nStartMonth);
    	ENDIF;
        
        sMonthCode = CellGetS(sCasCube, sStartMonth, sKodMesice);
        
        CellPutN(sMonthCode, sTargetCube, psDcerSpol, vsGUV, sElDruhyVozu, sElZavod, sElTemata, sStartMonth, psVerze, sElVstup);
        
        
    nStartMonth = nStartMonth + 1;
    sStartMonth = NumberToString(nStartMonth);
    
END;
Whole point of this is so that the CellPutN does this:
CellPutN(JAN, sTargetCube...)
CellPutN(FEB, sTargetCube...)
CellPutN(MAR, sTargetCube...)
...

When I try to save this i get an error message: Syntax error on or before: " sMonthCode, sTargetC invalid numeric expression "
If I change it to CellPutS I can save it but when I run it I of course get error message about trying to insert text into the numeric leaf cells.

Any idea how to get around this problem? Is what I am attempting to do even possible? Thanks.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Identifying column names in datasource with variables

Post by lotsaram »

Yes this is absolutely possible. Usually you use the default v1 to vN naming for the variables and have a hardcoded declarations on the prolog of the variable fields to map the v1 to vN to descriptive variable names. Then on the Data tab you use a while loop and the Expand function to loop through the variable columns and assign values to the variables you want to use.

Look up and understand the Expand function. It is one of the most powerful features of TI.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
vasek1192
Posts: 47
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Version: 2.0.9.3
Excel Version: 2019

Re: Identifying column names in datasource with variables

Post by vasek1192 »

Sorry for forgetting to reply, your solution worked great, thanks :)
Post Reply