topic Re: Convert Rows into Columns with 2 fields in the table in App Development

Convert Rows into Columns with 2 fields in the table

venkatesh7 — Mon, 13 Feb 2023 18:40:51 GMT

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😊

Re: Convert Rows into Columns with 2 fields in the table

rwunderlich — Tue, 14 Feb 2023 01:00:42 GMT

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

Re: Convert Rows into Columns with 2 fields in the table

vinieme12 — Tue, 14 Feb 2023 08:29:55 GMT

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;

Re: Convert Rows into Columns with 2 fields in the table

venkatesh7 — Sun, 19 Feb 2023 16:16:32 GMT

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