Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

try to nest a subfield loop

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


IDPad
Stand
011,2  
H11, G21
021,1
D1, S31
031,1,3D4, F1 , E12


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


The best result on loading data should be


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


As now I'm only get this

IDPadStand
011H11
012G21
021D1
021S31

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

1 Solution

Accepted Solutions

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

Hi,

maybe like this?:

QlikCommunity_Thread_130987_Pic2.JPG.jpg

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

6 Replies

Re: try to nest a subfield loop

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

MVP
MVP

Re: try to nest a subfield loop

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Re: try to nest a subfield loop

not exactly, but maybe helps:

QlikCommunity_Thread_130987_Pic1.JPG.jpg

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

Hi,

maybe like this?:

QlikCommunity_Thread_130987_Pic2.JPG.jpg

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

Not applicable

Re: try to nest a subfield loop

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

you're welcome

regards

Marco

Community Browser