topic Re: try to nest a subfield loop in QlikView

try to nest a subfield loop

Wed, 27 Aug 2014 14:44:37 GMT

hello I'm trying to load this table in order to group values according to an established order.

the data in two columns separated by comma

ID	Pad	Stand
01	1,2	H11, G21
02	1,1	D1, S31
03	1,1,3	D4, F1 , E12

the first Pad matches the first of the Stand column and so on..

The best result on loading data should be

ID	Pad& Stand
01	Pad. 1 Stand. H11 - Pad 2 Stand G21
02	Pad. 1 Stand. D1 S31
03	Pad. 1 Stand. D4 F1 - Pad. 3 Stand. E12

As now I'm only get this

ID	Pad	Stand
01	1	H11
01	2	G21
02	1	D1
02	1	S31

..and so on

this is tehe load script that i'm using to try to get the result:

for i=1 to 10

LOAD ID,

subfield(purgechar(Pad, chr(10)),',',$(i)) as Pad,

subfield(purgechar(Stand, chr(10)),',',$(i)) as Stand,

FROM

(ooxml, embedded labels, table is Recuperati_Foglio1);

NEXT i;

Someone could help me on this?

thnks

Stefano

Re: try to nest a subfield loop

MarcoWedel — Thu, 28 Aug 2014 11:24:35 GMT

try with subfield(), IterNo() and Concat()

Re: try to nest a subfield loop

jonathandienst — Thu, 28 Aug 2014 11:38:08 GMT

No need for a loop. Subfield(2 parameters) takes care of that for you:

T_Data:

LOAD

'Pad ' & Pad & ' Stand ' & Stand As EachCombo

;

LOAD ID,

subfield(purgechar(Pad, chr(10)),',') As Pad,

subfield(purgechar(Stand, chr(10)),',') As Stand,

FROM ...

Results:

LOAD Concat(EachCombo, ' - ') As [Pad&Stand]

Resident T_Data;

This will create records for all combinations of Pad and Stand and will list the combinations in the Pad&Stand field.

HTH

Jonathan

Re: Re: try to nest a subfield loop

MarcoWedel — Thu, 28 Aug 2014 12:09:16 GMT

not exactly, but maybe helps:

LOAD ID,
    Concat('Pad. '&Pad&' Stand. '&Stand, ' - ', OrdNo) as [Pad& Stand]
Group By ID;
LOAD ID,
    SubField(Pad, ',', IterNo()) as Pad,
    SubField(Stand, ',', IterNo()) as Stand,
    IterNo() as OrdNo
FROM [http://community.qlik.com/thread/130987]
(html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 5))))
While IterNo() <= If(SubStringCount(Pad,',')>SubStringCount(Stand,','), SubStringCount(Pad,','), SubStringCount(Stand,','))+1;

regards

Marco

Re: Re: Re: try to nest a subfield loop

MarcoWedel — Thu, 28 Aug 2014 12:18:43 GMT

Hi,

maybe like this?:

LOAD ID,
     Concat('Pad. '&Pad&' Stand. '&Stand, ' - ', RecNo()) as [Pad& Stand]
Group By ID;
LOAD ID,
     Pad,
     Concat(Stand, ' ', RecNo()) as Stand
Group By ID, Pad;
LOAD ID, 
     SubField(Pad, ',', IterNo()) as Pad, 
     SubField(Stand, ',', IterNo()) as Stand
FROM [http://community.qlik.com/thread/130987]
(html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 5))))
While IterNo() <= If(SubStringCount(Pad,',')>SubStringCount(Stand,','), SubStringCount(Pad,','), SubStringCount(Stand,','))+1;

hope this helps

regards

Marco

Re: try to nest a subfield loop

Thu, 28 Aug 2014 13:11:23 GMT

Hi Marco Great job

I have only add again the purgechar for get all the data (some dirty spaces in excel) and works perfectly..i don't know how but works

thanks a lot you save me

tabData:

LOAD ID,

Concat('Pad. '&Pad&' Stand. '&Stand, ' - ', RecNo()) as [Pad& Stand]

Group By ID;

LOAD ID,

Pad,

Concat(Stand, ' ', RecNo()) as Stand

Group By ID, Pad;

LOAD ID,

SubField(purgechar(Pad, chr(10)), ',', IterNo()) as Pad,

SubField(purgechar(Stand, chr(10)), ',', IterNo()) as Stand

FROM

(ooxml, embedded labels, table is Recuperati_Foglio1)

While IterNo() <= If(SubStringCount(Pad,',')>SubStringCount(Stand,','), SubStringCount(Pad,','), SubStringCount(Stand,','))+1;

Re: try to nest a subfield loop

MarcoWedel — Thu, 28 Aug 2014 13:22:06 GMT

you're welcome

regards

Marco