Filter on specific values in load script

[vkal12]

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!

[ogster1974]

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.

[vinieme12]

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;