Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello World,
I am new to Qlik, struggling with some data filtering exercise:
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 |
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:
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.
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:
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:
Table1:
LOAD
"Sales Order",
"Delivery Number",
"Shipment Number",
FinalStatus,
"Shipment Date"
FROM [lib://Folder/filename.xlsx]
(ooxml, embedded labels, table is Sheet1);
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.
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.
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:
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.