How to transform variable values from excel into QlikView table

[REON_LEE]

Hi All,

Anyone can suggest to me how to transform the list of variable values from excel into the table.

I have defined 2 columns, the variable name(Variable) & variable values(VarExpression).

My initial intention was to load the variable into QlikView , then match the list of values during the where clause. 

But I failed, this is because some values are in the number & text format.

Variable	VarExpression
vFinPurchaseGood	101,102,903,904,Z01,Z02,561,912,Z05,Z06
vFinAdjustment	905,906,907
vFinTransferOut	Z03,641,

The result will look like below:

Variable	VarExpression
vFinPurchaseGood	101
vFinPurchaseGood	102
vFinPurchaseGood	903
vFinPurchaseGood	904
vFinPurchaseGood	Z01
vFinAdjustment	905
vFinTransferOut	Z03

.......cont.

Any suggestion is highly appreciated.

Regards,

Reon

[sunny_talwar · Accepted solution]

Try SubField function

Table:
LOAD Variable,
	 SubField(VarExpression, ',') as VarExpression;
LOAD * INLINE [
    Variable, VarExpression
    vFinPurchaseGood, "101,102,903,904,Z01,Z02,561,912,Z05,Z06"
    vFinAdjustment, "905,906,907"
    vFinTransferOut, "Z03,641"
];

[REON_LEE]

Hi Sunny,

Your suggestion is very useful, Thank you very much!