topic Re: split a key value table into multiple columns in App Development

split a key value table into multiple columns

egma — Fri, 20 May 2022 14:50:50 GMT

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

Re: split a key value table into multiple columns

edwin — Fri, 20 May 2022 20:54:49 GMT

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

Re: split a key value table into multiple columns

edwin — Fri, 20 May 2022 20:56:57 GMT

obviously you will modify it to use 7 as your divisor

and this assumes the table is already in the right order

Re: split a key value table into multiple columns

MarcoWedel — Sat, 21 May 2022 10:39:46 GMT

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

Re: split a key value table into multiple columns

egma — Wed, 25 May 2022 12:39:59 GMT

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!!!