split a key value table into multiple columns

[egma]

Hi guys,

After making a call to an external API I got the data into qlik sense in the format of KEY-VALUE pairs:

I need to transform this table into something like this where each row with the same key goes as the value of a separate column like this:

I tried for loops, it works but is really slow (until 3 hours to reshape the data).  Do you have another approach to recommend me to unfold the data?

Thanks in advance for any help or advice you can give me,

Carlos

[edwin]

for loops work great with small volumes but as youve found out its very slow.  table opertaions are much faster.  you can label your rows with row numbers, add an index to each value, and add a group.

for example:
load
floor(rand()*1000) as volume,
rowno() as row,
floor(rowno()/5) as group,
mod(RowNo()-1,5) as order
while iterno()<20;
load 1 autogenerate(1);

you can event conver the order as values:
pick(mod(RowNo()-1,5)+1,'1st','2nd','3rd','4th','5th') as order

you can actually stop there and just lookup the value using set analysis 
{<order={'2nd'}>}

if you still need to pivot the table you can aggregate it 

t:
load floor(rand()*1000) as volume, rowno() as row, floor(rowno()/5) as group,
pick(mod(RowNo()-1,5)+1,'1st','2nd','3rd','4th','5th') as order
while iterno()<20;
load 1 autogenerate(1);

NoConcatenate
temp:
load
[group],
if([order]='1st', (volume)) as first,
if([order]='2nd', (volume)) as sec,
if([order]='3rd', (volume)) as third,
if([order]='4th', (volume)) as fourth,
if([order]='5th', (volume)) as fifth
Resident t
;

NoConcatenate
d:
load
[group],
(max(first)) as first,
(max(sec)) as sec,
(max(third)) as third,
(max(fourth)) as fourth,
(max(fifth)) as fifth
Resident temp
group by [group]
;
drop table temp;
exit script;

here is another way of pivoting:
https://community.qlik.com/t5/App-Development/how-to-pivot-in-load-script/td-p/1513761

[edwin]

obviously you will modify it to use 7 as your divisor

and this assumes the table is already in the right order 

[MarcoWedel · Accepted solution]

Hi,

one solution might be as well:

mapFields:
Mapping
LOAD 'field'&RecNo(),
     Field 
Inline [
Field
URLid
URL
views
downloads
sessions
visitors
timeonpage
];

table1:
LOAD *, If(key=Previous(key),Peek(FieldNo)+1,1) as FieldNo
Inline [
key, value
1, URLid 1
1, URL 1
1, 10
1, 11
1, 12
1, 13
1, 14
2, URLid 2
2, URL 2
2, 20
2, 21
2, 22
2, 23
2, 24
3, URLid 3
3, URL 3
3, 30
3, 31
3, 32
3, 33
3, 34
];

table2:
Generic
LOAD key,
     'field'&FieldNo,
     value 
Resident table1;

DROP Table table1;

table3:
LOAD 1 as tempfield AutoGenerate 0;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'table2.*') THEN
    JOIN ([table3]) LOAD * RESIDENT [$(vTable)];
    DROP TABLE [$(vTable)];
  ENDIF
NEXT i

DROP Field tempfield;

RENAME Fields using mapFields;

hope this helps

Marco

[egma]

Thanks a lot guys for all your ideas 😊 and especially to @MarcoWedel . Your solution worked like a charm, I went from 3 hours to less than 7 seconds in data reshaping 🚀...that's what I call a huge improvement.

You're all amazing guys!!!