TM1 Forum

Posted: **Wed May 05, 2021 1:41 pm**

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 (59.62 KiB) Viewed 9658 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.

Posted: **Wed May 05, 2021 3:48 pm**

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.

Posted: **Wed Jul 07, 2021 12:01 pm**

Sorry for forgetting to reply, your solution worked great, thanks

TM1 Forum

Identifying column names in datasource with variables

Identifying column names in datasource with variables

Re: Identifying column names in datasource with variables

Re: Identifying column names in datasource with variables