Using Expand Function to make Variable Dynamic in TI

[manpreet]

Hi Team,

I have a CSV in which I have 20 Columns and this can change in future that is the reason I want to make a dynamic TI.
So that in future if some added more columns then it should directly pick no need to add more hardcoded rows.

CSV as Source.png (8.63 KiB) Viewed 1295 times

Data source Tab screenshot in TI.

TI Data Source Tab.png (41.71 KiB) Viewed 1295 times

Variable Tab Screenshot in TI.

TI Variable Tab.png (53.77 KiB) Viewed 1295 times

TI Code :

### Prolog ###
vCount = 0;
vCountCheck = 0;
nCounter=0;

### Meta Data ###
vCount = vCount +1;
vMinLoop=1;
vMaxLoop=19;

While(vMinLoop<=vMaxLoop);

Variable='V'|NumberToString(vMinLoop);
Value=Expand ( '%' | Expand(Variable)| '%');

nCounter=nCounter+1;
TextOutput('C:\Manpreet Dump\ExpandFunctionTesting1.txt',Variable,Value,NumberToString(nCounter));

IF(vCount=1);
vElement=Value;
TextOutput('C:\Manpreet Dump\ExpandFunctionTesting2.txt',vElement);
Endif;
IF(vCount>1);
vData=Value;
TextOutput('C:\Manpreet Dump\ExpandFunctionTesting3.txt',vData);
TextOutput('C:\Manpreet Dump\ExpandFunctionTesting4.txt',vElement,vData);
Endif;

vMinLoop=vMinLoop+1;
END;

[manpreet]

Attached Files of Dump by TextOutPut :

ExpandFunctionTesting1.txt

(1.17 KiB) Downloaded 95 times

ExpandFunctionTesting2.txt

(607 Bytes) Downloaded 87 times

ExpandFunctionTesting3.txt

(201 Bytes) Downloaded 82 times

[manpreet]

ExpandFunctionTesting4.txt

(790 Bytes) Downloaded 87 times

Now the issue is that 1 Row is My Element Name in Dimension and from 2 Row it is data to be uploaded in specific Element which is in 1row

taking example of one element only so now in my dump it is

1 "Pipeline Project Description"
2 "DFADFA"

I want it to be 1 line "Pipeline Project Description" , "DFADFA"

so that i can do CellputN/S example : CellputS(vData,CubeName,vPID,vElement);

vPID will be a new element created in this as serial for every record example row 2 vPID is L0001 Row vPid will be L0002 same looping like v element will be done here also for every record once vPID is created. Currently i didn't write the code because first i want to check if can get the CSV to a proper upload format or you can say i am trying to do a kind of transpose.

and my 1 row should repeat for below all the rows in which i have data example now it is on 2 nd row only but if 2-100 then for all i should get the data.

Regards,
Manpreet

[gtonkin]

Without going through all the code, just the concept, I did something a few years ago where I just set an alias on the measures that I was loading into e.g. COL1, COL2 etc.

Using a loop and expand I could derive the COL1, COL2, test if it matched to an element in measures, possibly use DType to determine CellIncrementN/S and upload the data.

[manpreet]

Hi gtonkin,

That part is okay but how i need vElement and VData side by side is a problem i achieved this by hardcoding for the title record.
but don't want to hard the title record as well.

Expected output which i need which i got after hardcoding the title like

IF(Count=1);
vElement1=v1;
Endif;

then it generates output like this "Pipeline Detail","DFDDA" now i can do upload it using CellputN.

but i wanted to keep this part also as generic vElement1=v1;

[Steve Rowe]

I think you are asking about how to collect the row headers so that you can use them as measure values to write the data to cube.
The problem with expand is that you can only use it on the right hand side and so it is "only" useful for reading variable values.
If we had something like it on the left hand side then we could get close to array variable types which we could write to.
But we don't so...

We already have array like data structures though, a cube.

So to solve your problem write your column headers to a cube and then read them back in your expand loop.

If you want to be really flash you can build and tear down your array cube on the fly within the job itself. (but be aware this would cause locking issues in long running or multi threaded jobs...)

CHeers,

[gtonkin]

Had some time this morning to explain my thoughts better:

Starting with a file containing my data per below in Sales.csv.

I have Product which is my "key" and 3 other columns I need to import into my Sales cube.

I create a new TI and name the variables simply V1, V2, V3, etc.

I then set the attributes on my Sales Measures to align to V1, V2, V3 etc. per my Sales Data:

Sales Part 1.png (196.23 KiB) Viewed 1189 times

Back in my TI I can write the code needed to process each field in the source file and write it to the mapped Sales Measures:

Code: Select all

sProduct=V1;

nIndexMax=4;
nIndex=1;
WHILE(nIndex<=nIndexMax);
	sDataSourceVariable='V' | NumberToString(nIndex);
	sDataSourceData=Expand('%' | sDataSourceVariable | '%');

	#--Get the measure name from the alias if it exists
	sMeasure=DimensionElementPrincipalName('Sales Measures', sDataSourceVariable);

	IF(sMeasure@<>sDataSourceVariable);
		#--Deal with numeric and strings separately
		IF(DType('Expense Measures', sMeasure)@='N');
			nFieldValue=NUMBR(sDataSourceData);
			CellPutN(nFieldValue, 'Sales', sProduct, sMeasure);
		ELSE;
			CellPutS(sDataSourceData, 'Sales', sProduct, sMeasure);
		ENDIF;
	ENDIF;

	#--Left this in to allow review and testing
	asciioutput('debug.txt',sDataSourceVariable, sDataSourceData, sMeasure);
	nIndex=nIndex+1;
END;

After running my code I have the following:

Sales After Import.png (14.3 KiB) Viewed 1189 times

Hope that illustrates better what I was trying to explain.

Apologies for the collage - post would not let me add more than 3 images

[manpreet]

Hi gtonkin,

Thanks for the help !!!

I Used the method suggested by you just a tweak instead of using Sales Measure as a Dimension I Created a Variable Dimension in which i have Element from V1 to V57 and there in Attribute i am storing the Header Value with this it Makes my Code more Dynamic so that in future if variable increases i just need to add more elements in variable Dimension.

Regards,
Manpreet

[gtonkin]

Excellent - glad it made sense and you were able to solve your issue.