Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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,
thanks for that, this is what the expected output which I'm looking for.