Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My fellow qlik-a-teers, I need your help.
I have a issue that once again a query/DBA logic would solve very quickly but in Qlik-a-tania (aka Qlik Sense Feb 2022 ver) I can't seem to crack it. I got this load script but the id is not the same in the three tables for all the records so instead of INNER KEEP; I tried Left Join and ended up with cartesian join and the data went from 119 Mb to 136 Gb. I need to 'sometimes' join on id when they line up and 'sometimes' just bring over the rows in tables 2/3 when they don't. In db land (or really every other BI Tool ever made for 30+ years; except this one) that is either a 'left outer join' or a 'full outer join' but in Qlik Sense I have no idea why it isn't working when I use LEFT JOIN (TABLE_NAME). INNER KEEP works but leaves out the mis-matched IDs; LEFT JOIN (TABLE) causes each table to re-join back on the top table and itself making each row 100 copies of itself until it reaches billions of rows.
Example: Table-1 ID, Name, Org, Last_Day_Worked 123, Smith, Corp, 2023-04-12 12:00:00 456, Jane, Sales, 2023-04-11 12:00:00 789, Julie, IT, 2023-04-12 12:00:00 Table-2 ID, Schedule, TimeZone 123, M-F, PST 123.1, M-Th, EST 123.2, Fr, CST 456, M-F, PST 789, M-F, CST Table-3 ID, Proj_Name,ProjStatus, StartDate 123, Sell, In-Progress, 20230402 123.1, Merger, Complete, 20230301 123.2, Purchase, Pending, 20230401 456, New_Fleet, In-Progress, 20230201 789, Upgrade_Laptops, Pending, 20230501 Output SB: ID, Name, Org, Schedule, TimeZone, Proj_Name, ProjStatus 123, Smith, Corp, M-F, PST, Sell, In-Progress 123.1, , , , , Merger, Complete 123.2, , , , , Purchase, Pending 456, Jane, Sales, M-F, PST, New_Fleet, In-Progress 789, Julie, IT, M-F, CST, Upgrade_Laptops, Pending or if possible ID, Name, Org, Schedule, TimeZone, Proj_Name, ProjStatus 123, Smith, Corp, M-F, PST, Sell, In-Progress 123.1, Smith, Corp, M-Th, EST, Merger, Complete 123.2, Smith, Corp, Fr, CST, Purchase, Pending 456, Jane, Sales, M-F, PST, New_Fleet, In-Progress 789, Julie, IT, M-F, CST, Upgrade_Laptops, Pending
Sample LOAD:
table_1: NoConcatenate LOAD id, name, org, Last_Day_Worked FROM [lib://my.qvd](qvd); INNER KEEP //LEFT JOIN (table_1) causes a cartesian join? table_2: load id, schedule, TimeZone FROM [lib://my_other.qvd](qvd); INNER KEEP //LEFT JOIN (table_1) causes a cartesian join? table_3: load id, Proj_Name, ProjStatus, StartDate FROM [lib://my_other_other.qvd](qvd);
Table1:
Mapping LOAD ID,
Name
& '|' &
Org
Inline [
ID, Name, Org, Last_Day_Worked
123, Smith, Corp, 2023-04-12 12:00:00
456, Jane, Sales, 2023-04-11 12:00:00
789, Julie, IT, 2023-04-12 12:00:00
];
Table2:
Mapping LOAD ID,
Schedule
& '|' &
TimeZone
Inline [
ID, Schedule, TimeZone
123, M-F, PST
123.1, M-Th, EST
123.2, Fr, CST
456, M-F, PST
789, M-F, CST
]
Where WildMatch(ID,'*.*') = 0;
Table3:
LOAD *,
SubField(ApplyMap('Table1',ID,Null()),'|',1) as Name,
SubField(ApplyMap('Table1',ID,Null()),'|',2) as Org,
SubField(ApplyMap('Table2',ID,Null()),'|',1) as Schedule,
SubField(ApplyMap('Table2',ID,Null()),'|',2) as TimeZone
Inline [
ID, Proj_Name,ProjStatus, StartDate
123, Sell, In-Progress, 20230402
123.1, Merger, Complete, 20230301
123.2, Purchase, Pending, 20230401
456, New_Fleet, In-Progress, 20230201
789, Upgrade_Laptops, Pending, 20230501
];
DROP Field StartDate From Table3;
EXIT SCRIPT;
Table1:
Mapping LOAD ID,
Name
& '|' &
Org
Inline [
ID, Name, Org, Last_Day_Worked
123, Smith, Corp, 2023-04-12 12:00:00
456, Jane, Sales, 2023-04-11 12:00:00
789, Julie, IT, 2023-04-12 12:00:00
];
Table2:
Mapping LOAD ID,
Schedule
& '|' &
TimeZone
Inline [
ID, Schedule, TimeZone
123, M-F, PST
123.1, M-Th, EST
123.2, Fr, CST
456, M-F, PST
789, M-F, CST
]
Where WildMatch(ID,'*.*') = 0;
Table3:
LOAD *,
SubField(ApplyMap('Table1',ID,Null()),'|',1) as Name,
SubField(ApplyMap('Table1',ID,Null()),'|',2) as Org,
SubField(ApplyMap('Table2',ID,Null()),'|',1) as Schedule,
SubField(ApplyMap('Table2',ID,Null()),'|',2) as TimeZone
Inline [
ID, Proj_Name,ProjStatus, StartDate
123, Sell, In-Progress, 20230402
123.1, Merger, Complete, 20230301
123.2, Purchase, Pending, 20230401
456, New_Fleet, In-Progress, 20230201
789, Upgrade_Laptops, Pending, 20230501
];
DROP Field StartDate From Table3;
EXIT SCRIPT;
Thank you sir for your example and screen shot, can you explain why the use of 'mapping' and 'wildmatch' =0?
Aside it being my preference, importantly, because with mapping I need not worry about duplicate data as I something do with joining. Notice how it allows me to define what to return when a value is not found in the mapping table, in this case Null().
The WildMatch filtering was only to prevent IDs like 123.1 & 123.2 from coming through, hence displayed as null like the expected output.
Thank you very much sir, this did exactly what I wanted!