Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Yean
Contributor II
Contributor II

How to exclude the whole row of data if there is negative value?

Yean_0-1597368641755.png

 

Hello, I have a table similar to the screenshot above. There are some negative values which is abnormal data and we want to ignore the whole row of data if there is negative value. So for above table, row 5 until row 10 should be ignored from the table in Qlik Sense. Anyone have any idea how do I do that?  Thank you in advance !

Labels (1)
9 Replies
usharanirangana
Contributor II
Contributor II

try this =if (sum(ACQty)>0,sum(ACQty),0) in expression. it will show only positive values.

Saravanan_Desingh

Use the Where clause in the LOAD to ignore. Something like.

LOAD ..
..
Where Field1>0 And Field2>0 And Field3>0
;
Kushal_Chawda

I can see in many columns you have a negative values. Do you want to check for specific column or all the columns for negative values?

alex00321
Creator II
Creator II

Hi Please find if attached script is okay to use. If so please mark this as answer.

Yean
Contributor II
Contributor II
Author

Thank you all for ur help. @Kushal_Chawda I'm suppose to check for all columns for negative values, but I realised there's a pattern in the data that I have, whatever column have negative value, the NCQty column will always have negative data too. So what i did is this: Sum({$<[DailyOMHSummary.NcQty]-={"<0"}>}[DailyOMHSummary.TotalQty])

For now, it solved my problem, but I'm thinking if let's say in the future this pattern is no more legit, is there a way to ignore all negative values in all the columns? 

Yean
Contributor II
Contributor II
Author

Hi, thanks for ur help but I couldn't load qvw file as I'm not using Qlik Sense Desktop. I'm using web version and everything is locked by IT so i couldn't load this qvw file. Can you just paste the script instead of attaching file? Thank you.

Yean
Contributor II
Contributor II
Author

Ahhh I just understood @Saravanan_Desingh  comment. So meaning if I want to ignore all negative values in all columns then I have to use the Where clause in the LOAD? Am I right? 

Kushal_Chawda

you can use something like below

Sum({$<Field1={">0"}>+<Field2={">0"}>+<Field2={">0"}>}[DailyOMHSummary.TotalQty])

or

Sum({$<Field1={">0"},Field2={">0"},Field2={">0"}>}[DailyOMHSummary.TotalQty])

Saravanan_Desingh

Yes, using the Where Clause in the LOAD is one of the approach