topic Re: How to join rows where the ID is same in QlikView

How to join rows where the ID is same

vikasshana — Thu, 14 Dec 2023 16:50:29 GMT

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,

Re: How to join rows where the ID is same

Daniel_Pilla — Thu, 14 Dec 2023 19:46:52 GMT

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,

Re: How to join rows where the ID is same

vikasshana — Fri, 15 Dec 2023 08:44:45 GMT

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