Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
try this =if (sum(ACQty)>0,sum(ACQty),0) in expression. it will show only positive values.
Use the Where clause in the LOAD to ignore. Something like.
LOAD ..
..
Where Field1>0 And Field2>0 And Field3>0
;
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?
Hi Please find if attached script is okay to use. If so please mark this as answer.
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?
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.
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?
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])
Yes, using the Where Clause in the LOAD is one of the approach