How to join rows where the ID is same

[vikasshana]

Hi,

I've below sample data

Table:
Load * Inline [
ID,CODE,PARTNER
1,WE,AB
1,AG,CD
1,RE,EF
];

FinalTable:
Load ID,
if(CODE = 'WE',PARTNER) as party1,
if(CODE = 'AG',PARTNER) as party2,
if(CODE = 'RE',PARTNER) as party3
Resident Table;

Drop Table Table;

Output Received:

Expected Output:

looking for load script to achieve above.

Regards,

v

[Daniel_Pilla · Accepted solution]

Hi @vikasshana ,

You are essentially asking for a reverse crosstable, which in Qlik is a generic load. The generic load process however results in multiple tables, so in my example below, I've joined them back together. After that process, we rename the fields according to your if() statements.

RawData:
LOAD * INLINE [
ID,CODE,PARTNER
1,WE,AB
1,AG,CD
1,RE,EF
];

GenericData:
GENERIC LOAD * RESIDENT RawData;

FinalTable:
LOAD DISTINCT ID RESIDENT RawData;

FOR i = NoOfTables()-1 to 0 STEP -1;
	LET vTable=TableName($(i));
    IF WildMatch('$(vTable)','GenericData.*') THEN
    	LEFT JOIN ([FinalTable]) LOAD * RESIDENT [$(vTable)];
		DROP TABLE [$(vTable)];
    ENDIF
NEXT i

Map_FieldNames:
MAPPING LOAD * INLINE [
	OLD		,NEW
    WE		,party1
    AG		,party2
    RE		,party3
];

RENAME Fields USING Map_FieldNames;
DROP TABLE RawData;

I referenced this post as part of my solution.

Cheers,

[vikasshana]

thanks for that, this is what the expected output which I'm looking for.