try to nest a subfield loop

[#1]

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

[MarcoWedel]

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

[jonathandienst]

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

[MarcoWedel]

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

[MarcoWedel · Accepted solution]

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

[#6]

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;

[MarcoWedel]

you're welcome

regards

Marco