Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
I have the following problem within my Qlik Sense App. I am joining to tables with the following statement in the load script:
Load
ID,
Date
Resident [Table1];
Inner Join
Load
ID,
Status
Resident [Table2];
This statement results in the following table:
The ID's 123 and 456 have two different status, basically one for the process start and one for the process end. In my output table I only want to show ID's with date and status where there is no status 95 available. So my output table should look like this:
If possible, I only want to change the script in the load editor so that I can use the output table directly in the frontend.
Can someone support me with a possible solution?
Thank you very much!
Create a mapping table that identifies all IDs with a 95 Status
Mapping
ID95Map:
Load
ID,
1 as Flag
Resident [Table2]
Where Match(Status,95);
then apply it to your dataset creating a flag.
Input:
Load
ID,
Date
Resident [Table1];
Inner Join (Input)
Load
ID,
Status,
ApplyMap('ID95Map',ID,0) as _95Flag
Resident [Table2];
Then you can either create an Output table excluding the IDs with 95 in a simple where clause or in the front end using the filter on flag.
As below
ExcludeIDs:
Load
ID as Exclude_ID,
Status
Resident [Table2]
Where Exists(Status,'95');
TableA:
Load
ID,
Date
Resident [Table1];
Inner Join(TableA)
Load
ID,
Status
Resident [Table2]
Where Not Exists(Exclude_ID,ID);
Drop Table ExcludeIDs;