Transpose a table in Load Script

[limu]

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! 

[lironbaram · Accepted solution]

hi 

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;

[limu]

Hello,

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

Have a nice day!