topic Re: Transpose a table in Load Script in QlikView

Transpose a table in Load Script

limu — Sat, 16 Nov 2024 18:19:31 GMT

Hello everyone,

I have a table that has the following structure:

RowNo	RowValue
1	ValueA
1	ValueB
2	ValueC
2	ValueD
3	ValueE
3	ValueF
...	...

I would like to transform it the above table into the following table:

Column1	Column2
ValueA	ValueB
ValueC	ValueD
ValueE	ValueF
...	...

Can someone provide me some tips/examples on how one could make such a transformtion?

Currently, I'm fetching only the data that have the same value on RowNo (i.e. I'm fetching each row separately) and then in a For loop I'm concatinating all those values into one string, separated by comma. But this is very slow, and it takes hours to complete the transformations on the data set that I have.

Thank you in advance!

Re: Transpose a table in Load Script

lironbaram — Thu, 23 Jul 2020 12:47:20 GMT

the way will be to use generic load for this

the script below will give you the result you are after

it follows this steps :

1. add a column to the data with values column1,column2, etc. dynamically

2. use generic load which result in number of table according to the distinct values in the column we crated in step 1;

3. unifying the tables from step 2 to one table according to the RowNo key

DataTemp: load * inline [ RowNo, RowValue, 1, ValueA 1, ValueB 2 ,ValueC 2, ValueD 3 ,ValueE 3, ValueF]; Data: load *, if(Previous(RowNo)=RowNo, rangesum(peek('counter'),1),1) as counter resident DataTemp order by RowNo; drop Table DataTemp; Data2: load RowNo, 'ColumnName' & counter as colName, RowValue Resident Data; drop Table Data; Data: Generic Load * resident Data2; drop table Data2; ///////////////////////////// let vTables= NoofTables(); let vDataTableName = TableName(0); RENAME Table $(vDataTableName) to Data; for i =1 to NoofTables()-1 Tables: load TableName($(i)) as TableName ,$(i) as RN AutoGenerate 1; NEXT for i=1 to NoofTables()-1 let vTableName = FieldValue('TableName', $(i)); if len('$(vTableName)')>1 then join (Data) load * resident $(vTableName) ; drop table $(vTableName); end if next drop Table Tables;

Re: Transpose a table in Load Script

limu — Thu, 23 Jul 2020 12:54:19 GMT

Hello,

thank you very much for your help! That is what I needed!

Have a nice day!