topic Re: Unnesting array-like fields in QV in QlikView

Unnesting array-like fields in QV

imark — Sat, 16 Nov 2024 04:07:51 GMT

Assume we are importing in QV an excel table looking as follows:

ID providers metric

101 google.com, yahoo.co.uk, seznam.ru 10

102 comcast.com, aol.net 21

103 mail.de 14

And that our goal is to "unnest" the provider field ending up, in this example, with 6 rows in the final table

ID providers metric

101 google.com 10

101 yahoo.co.uk 10

101 seznam.ru ´ 10

102 comcast.com 21

...

How can we achieve this in QV? I can think of a solution using two nested loops but was wondering any more code-efficient way.

Re: Unnesting array-like fields in QV

jpenuliar — Mon, 18 Mar 2019 15:21:27 GMT

Hi Imark,

Try this:

Temp00:
Load * Inline [
ID,PROVIDERS,METRIC
101,'google.com, yahoo.co.uk, seznam.ru',10
102,'comcast.com, aol.net',21
103,'mail.de',14
];

NoConcatenate
Load
ID,
SubField("PROVIDERS",',',IterNo()) as "PROVIDERS",
METRIC
Resident Temp00
WHILE IterNo() <= SubStringCount("PROVIDERS",',')+1
;

DROP table Temp00;

Re: Unnesting array-like fields in QV

Marcos_rv — Mon, 18 Mar 2019 15:22:21 GMT

Try this:

tablaaaaaa:
load *,
SubStringCount (providers, ',') as count;

load * inline [
ID , providers , metric

101 , "google.com, yahoo.co.uk, seznam.ru" ,10

102 , "comcast.com, aol.net" , 21

103 , "mail.de" , 14
];

let vVar = NoOfRows('tablaaaaaa');

for i = 0 to $(vVar) - 1

LET vID = peek ('ID', $(i), 'tablaaaaaa');
let vSUB = peek( 'count', $(i), 'tablaaaaaa');
LET vPROV = peek( 'providers', $(i), 'tablaaaaaa');

for j =0 to $(vSUB)

AUX_TABLA:
LOAD
$(vID) AS ID,
SubField('$(vPROV)',',',$(j)+1) as providers
AutoGenerate 1;

NEXT;

NoConcatenate
final_tabla:
load
ID,
providers
Resident AUX_TABLA;

LEFT JOIN (final_tabla)
LOAD
ID,
metric
Resident
tablaaaaaa;

DROP TABLE tablaaaaaa;
DROP TABLE AUX_TABLA;

exit script;

sorry for the names I use in the variables or tables, mmmm I take this kind of queries as a game, scripting is fun. Regards!!!!

Re: Unnesting array-like fields in QV

rwunderlich — Mon, 18 Mar 2019 17:54:48 GMT

There is no need for the IterNo(). Just use the two parameter SubField() and the additional rows will be created automatically:

Load
ID,
trim(SubField(PROVIDERS, ',')) as PROVIDERS,
METRIC
Inline [
ID,PROVIDERS,METRIC
101,'google.com, yahoo.co.uk, seznam.ru',10
102,'comcast.com, aol.net',21
103,'mail.de',14
];

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

Re: Unnesting array-like fields in QV

imark — Tue, 19 Mar 2019 10:17:06 GMT

Talk about a neat solution! Thank you a lot to you both