Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasshana
Creator II
Creator II

How to join rows where the ID is same

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:

vikasshana_0-1702572514783.png

Expected Output:

vikasshana_1-1702572573203.png

looking for load script to achieve above.

Regards,

v

1 Solution

Accepted Solutions
Daniel_Pilla
Employee
Employee

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,

View solution in original post

2 Replies
Daniel_Pilla
Employee
Employee

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
Creator II
Creator II
Author

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