How can i related the single row values to multiple row values?

[Anonymous]

My source is from an xml file which i then added into excel to look like this because it seem more better for me.

I  found part of the answer from this post Converting rows into multiple columns | Qlik Community which worked for me.

How can i related the single row values to multiple row values?

ID	NAME	GROUPS
123	William	1
123	William	23423
12314	RAJ	1
12314	RAJ	312
12314	RAJ	34
12314	RAJ	3242
23232	John	32
23232	John	43223
23232	John	122344
23232	John	1321

Load RowNo() as link, A as [ID]

FROM [SAMPLE>XLSX](ooxml, no labels, table is Sheet2)

where B = 'ID';

Left Join LOAD RowNo() as link, A as [NAME]

FROM [SAMPLE>XLSX](ooxml, no labels, table is Sheet2)

where B = 'NAME';

I was able to join the ids and names using above method but since there are multiple groups, this technique is not working for groups. Can you suggest something?

[kaushiknsolanki]

HI,

Try this script.

Data:

LOAD A,

     B,

     C,

     D,

     RowNo() as SR

FROM

[SAMPLE (1).xlsx]

(ooxml, no labels, table is Sheet1);

Final:

Load A as ID, SR as SR1

Resident Data where B='ID';

Left join(Final)

Load SR-1 as SR1,A as Name

Resident Data where B='NAME';

Left join(Final)

LOad C as Group,SR-D-2 as SR1

Resident Data where B = 'Groups' and D<>'Total';

Regards,

Kaushik Solanki

[tamilarasu]

Hello Vaibhav,

Data:
LOAD
If(Isnull(A), Peek(A), A) as A,
If(Peek(B) Like '*ID', Peek(A), Peek('ID')) as ID,
     B,
     C,
     D
FROM
[..\Downloads\SAMPLE (8).xlsx]
(ooxml, no labels, table is Sheet1);

Result:
Load RowNo() as RowID,
ID,
A as NAME,
C as GROUPS
Resident Data
Where Not WildMatch(B,'*ID','NAME') and D <> 'Total';

DROP Table Data;

Result: