Convert Rows into Columns with 2 fields in the table

[venkatesh7]

Hello Folks,

Have a sample data like below and want to convert into a table

Sample Data:

Load * inline [
Header, Data
ID, 201
Timestamp, 2023-02-08T06:35:12.500Z
Country, US
Sales, 150000
ID, 201
Timestamp, 2023-02-08T07:26:29.441Z
Country, US
Sales, 200000
ID, 202
Timestamp, 2023-02-09T10:36:19.241Z
Country, US
Sales, 180000
];

Expected Output:

id	country	timestamp	sales
201	US	2023-02-08T06:35:12.500Z	150000
201	US	2023-02-08T07:26:29.441Z	200000
202	US	2023-02-09T10:36:19.241Z	180000

Please help me out to achieve output like above

Thanks in Advance😊

[rwunderlich · Accepted solution]

You will need to assign a common identifier to the rows you want to group. I wasn't clear whether you meant for your sample ID values to be unique.  If they were unique, you could propagate the ID to each row using Peek(). In the example below I'm generating a new grouping field "Sequence" which will work if ID is unique or not. 

RawData:
LOAD *,
  alt(Peek('Sequence'), 0)  + (Header = 'ID') as Sequence
;
Load * inline [
Header, Data
ID, 201
Timestamp, 2023-02-08T06:35:12.500Z
Country, US
Sales, 150000
ID, 201
Timestamp, 2023-02-08T07:26:29.441Z
Country, US
Sales, 200000
ID, 202
Timestamp, 2023-02-09T10:36:19.241Z
Country, US
Sales, 180000
];

Props:
Generic Load Sequence, Header, Data
Resident RawData
;

//*** Optional - consolidate tables
Final:
Load Distinct Sequence Resident RawData;
Drop Table RawData;

For i = NoOfTables()-1 to 0 step -1
 Let vTable = TableName($(i));
 if '$(vTable)' like 'Props.*' THEN
  Join (Final) Load * Resident $(vTable);
  Drop Table $(vTable);
 ENDIF
Next i 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[vinieme12 · Accepted solution]

Here is an alternative script

Temp:
Load *,if(Header='ID',Data,Peek('RecKey')) as RecKey
, alt(Peek('Sequence'),0) + (Header = 'ID') as Sequence

inline [
Header, Data
ID, 201
Timestamp, 2023-02-08T06:35:12.500Z
Country, US
Sales, 150000
ID, 201
Timestamp, 2023-02-08T07:26:29.441Z
Country, US
Sales, 200000
ID, 202
Timestamp, 2023-02-09T10:36:19.241Z
Country, US
Sales, 180000
];

Set vFieldList = '';
for each Col_name in FieldValueList('Header')
let vFieldList = ',TextBetween(Rows,''$(Col_name)@'',''@'') as [$(Col_name)] ' & '$(vFieldList)' ;
next Col_name ;

let vFieldList = Mid('$(vFieldList)',2,len('$(vFieldList)'));

Main:
Load
$(vFieldList)
;
Load Sequence ,Concat(Header&'@'&Data&'@',',') as Rows
Resident Temp
Group by Sequence;

Drop table Temp;

[venkatesh7]

Thank you so much @rwunderlich and @vinieme12 both the solutions worked liked a charm!