topic Re: How to transform variable values from excel into QlikView table in QlikView

How to transform variable values from excel into QlikView table

REON_LEE — Wed, 30 Oct 2019 10:36:27 GMT

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

Re: How to transform variable values from excel into QlikView table

sunny_talwar — Wed, 30 Oct 2019 12:33:16 GMT

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" ];

Re: How to transform variable values from excel into QlikView table

REON_LEE — Wed, 30 Oct 2019 14:35:20 GMT

Hi Sunny,

Your suggestion is very useful, Thank you very much!