Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
Hello,
thank you very much for your help! That is what I needed!
Have a nice day!