Removing rows that does not meet condition

[user467341]

Hi.

I have a straight table like this:

Date	Item	Value
1/1/2023	Cat	13
1/1/2023	Dog	14
1/1/2023	Mouse	31
1/1/2023	-	-
2/1/2023	Cat	13
2/1/2023	-	-
3/1/2023	-	-
4/1/2023	-	-
5/1/2023	-	-
6/1/2023	Cat	33
6/1/2023	Fish	13
6/1/2023	Mouse	12

I want to remove rows where "Item" and "Value" are "-" and ONLY IF there are other records in that same date. For date where there is only one entry, which is "-" for both "Item" and "Value", it must remain in the table.

The end result I am looking for is:

Date	Item	Value
1/1/2023	Cat	13
1/1/2023	Dog	14
1/1/2023	Mouse	31
2/1/2023	Cat	13
3/1/2023	-	-
4/1/2023	-	-
5/1/2023	-	-
6/1/2023	Cat	33
6/1/2023	Fish	13
6/1/2023	Mouse	12

How can I achieve this in my straight table expression?

[Chirantha]

Hey!

In Qlik, you can achieve this with set analysis and an "if" statement in your expression.
 

Step 1: Add a new field in the data load script to identify dates with more than one record. This can be done using the "count" function in a "group by" clause:

Load Date, count(Date) as DateCount
Resident YourTable
Group by Date;

Step 2: In your straight table, use an "if" statement in your expression to exclude the "-" records for dates with more than one record:

If((Item <> '-' and Value <> '-') or DateCount = 1, YourExpression)


Please replace YourExpression with the actual expression you're using to calculate the measure in your straight table. Also, please replace YourTable with the actual name of your table.

For more information regarding if statements please refer to if - script and chart function | Qlik Cloud Help

[user467341]

Hi Chirantha. Is it possible to use purely expression instead of adding a table in load editor?

[user467341]

Hi Chirantha, I tried your method but the table is the same. I don't think this gets my end result table?