Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
try with subfield(), IterNo() and Concat()
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
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
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
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;
you're welcome
regards
Marco