topic Re: Filter data with two statuses in Connectivity & Data Prep

Filter data with two statuses

DmitryKras — Thu, 18 Nov 2021 09:00:00 GMT

Hello World,

I am new to Qlik, struggling with some data filtering exercise:

At first I have big table FactSales with quite a few columns including [Delivery Number], [Shipment Number] and [FinalStatus]
In all lines where for one [Delivery Number] there are lines with [Final Status]='SHIPPED' and any other status, I want all lines with other statuses dropped, i.e. not loaded to the target FactSales table
In any other case I want all lines loaded

For example, if original FactSales table is:

Sales Order	Delivery Number	Shipment Number	FinalStatus	Shipment Date
110301805	855892747	1291316	LATE	16/01/2021
110328392	855892747	1291316	LATE	16/01/2021
110328393	855892747	1291316	LATE	16/01/2021
110340103	855892747	1291316	LATE	16/01/2021
110301805	855892747	1292681	SHIPPED	20/01/2021
110328392	855892747	1292681	SHIPPED	20/01/2021
110328393	855892747	1292681	SHIPPED	20/01/2021
110340103	855892747	1292681	SHIPPED	20/01/2021

Then the target table should look like:

Sales Order	Delivery Number	Shipment Number	FinalStatus	Shipment Date
110301805	855892747	1292681	SHIPPED	20/01/2021
110328392	855892747	1292681	SHIPPED	20/01/2021
110328393	855892747	1292681	SHIPPED	20/01/2021
110340103	855892747	1292681	SHIPPED	20/01/2021

Re: Filter data with two statuses

Andrei_Cusnir — Thu, 18 Nov 2021 13:52:17 GMT

Hello,

I am not 100% sure what do you mean with "not loaded to the target FactSales table" statement, however I have covered 2 different use case scenarios, so hopefully one of them suits your needs.

1. If you need to create a Table chart and just display the records where FinalStatus equals to "SHIPPED", then you can do the following:

This is the dataset that I have used:
All of the columns are Dimension fields that were added from the loaded dataset
Now if you create a copy of the table and remove the Dimension "FinalStatus", you can add it as Measure.
Click on "Add column" and then "Measure"
Use the following expression: If(FinalStatus='SHIPPED', FinalStatus, Null())
This expression checks to see if the FinalStatus is equal to "SHIPPED" and in that case it displays the value of FinalStatus, or otherwise it will display a NULL value.
You will notice that all the other columns have "Search" icon on the header, which means that you can filter them, as they are fields associated with the values in your dataset, but in the new column the "Search" icon is not present. Because this column is a Measure and not a Dimension.
Then under "Add-ons > Data handling" you can un-check the option "Include zero values"
You will notice that the new table only displays the records where "FinalStatus" equals to "SHIPPED"

2. If you would like to "load" records to a new table via the Data load editor, where the new table will have only the records with "FinalStatus" equals to "SHIPPED", then you can follow the steps below:

In Data load editor you can have the following script for loading all the values:

Table1:
LOAD
"Sales Order",
"Delivery Number",
"Shipment Number",
FinalStatus,
"Shipment Date"
FROM [lib://Folder/filename.xlsx]
(ooxml, embedded labels, table is Sheet1);

You can create a new load statement as follows:

Table2:
LOAD
"Sales Order" as "Sales Order 2",
"Delivery Number" as "Delivery Number 2",
"Shipment Number" as "Shipment Number 2",
FinalStatus as "FinalStatus 2",
"Shipment Date" as "Shipment Date 2"
Resident Table1
WHERE FinalStatus = 'SHIPPED';

This will create a new table that will only have the records that you need:

I hope that this information was helpful. In case this answer has helped you resolve the issue, please mark it as solution so other community members could also find it. Otherwise, in case I have misunderstood the use case scenario, please elaborate in detail by providing additional information.

Re: Filter data with two statuses

DmitryKras — Thu, 18 Nov 2021 14:59:09 GMT

Hello Andrei,

Thanks for your prompt response. Unfortunately, both of your answers don't help me. Let me try to restate what I need. First of all, whatever I want to do should happen in data load (data script).

I don't need simply filter resident table with [FinalStatus]='SHIPPED'. In each row there's some [Delivery Number], this field is not unique as for one Delivery there could be two and more rows with different [Shipment Number]. If for one delivery there are 2 shipments with different statuses AND one of those statuses = SHIPPED, I don't want to load any other rows with [FinalStatus] other than 'SHIPPED'. If among those statuses for one delivery there's no 'SHIPPED', we load all rows, do not drop anything.

Hope that helps.

Re: Filter data with two statuses

Andrei_Cusnir — Wed, 24 Nov 2021 15:12:50 GMT

Hello,

Thanks for sharing this information with us. It is now more clear as to what exactly is the use case scenario. The reason I got confused, is because for your sample data you have share the following (simplified) example:

And the expected output you have specified as (simplified):

However, in the particular example your use case scenario is not very clear, because you are just getting all the records where FinalStatus = "SHIPPED", since we can't see different examples of different Shipment Numbers. My assumption is that you would like to have the following dataset:

And you want to write a script in Data load editor where you will create a new table with the following outcome:

This is the breakdown of the resulted table:

Both Delivery Numbers "DN_A" are kept, because for 2 different Serial Numbers, both of them have status "LATE"
From the Delivery Number "DN_B" we are keeping only the record with Serial Number "SN_D", because the other has status "LATE", but since there is already at least one record with status "SHIPPED", we are keeping only that record
From the Delivery Number "DN_C" we are keeping the Shipment Numbers "SN_E" and "SN_G", because at least one of the Shipment Numbers has status "SHIPPED" so all the records with "LATE" statues are ignored.
Finally for Delivery Number "DN_D", we are keeping the record, because it is the only one for that Delivery Number and thus since its status is "LATE" we are keeping it.

If my understanding is correct, then to resolve this in a chart you would use a set analysis in an expression as:

Count({<FinalStatus={"SHIPPED"}, [Delivery Number]={"DN_C"}>} TOTAL FinalStatus)

This set analysis counts all the FinalStatus field's values where FinalStatus equals to SHIPPED and at the same time the Delivery Number equals to DN_C. Ideally this could have been

used in a For loop, where you can check each record of the original table and execute this expression for every Delivery Number. If the count is grater than 0 then you should load the row that you are checking only if the status equals to SHIPPED, otherwise, you should ignore it. But if the count is 0, then all the records for that Delivery Number are LATE, which means that you pass the record to the new table as it is without additional checks.

However, this is an ideal situation that is not supported unfortunately, because you can't use Set Analysis in Data load editor script. Therefore, in this use case scenario, I would suggest you to load all the data and then limit the views in the visualizations by using similar logic with the expression that I have provided above. Alternatively, you can try to see if you can implement the logic of the expression, within the Data load editor script, with combination of multiple function such as Peek(). However this seems to be super complicated for your use case scenario, if not impossible.

I hope that this information was helpful.