topic Re: How to filter out zero rows within an expression field (in Qlikview)? in QlikView

How to filter out zero rows within an expression field (in Qlikview)?

joeluqlik — Wed, 25 Nov 2020 16:16:04 GMT

Row	Report 1	Report 2	Difference (an expression of Report 1 minus Report 2)
1	5000	6000	-1000
2	400	400	0
3	2000	1800	200
4	500	500	0

I tried to filter out rows (#2, #4 in this case) where the value is zero within the Difference column which is an expression of comparing the two reports (Report 1 and Report 2). But can't seem to select exclude the zeros when the field is not a dimension but and expression. Can't use Supress Zero function to do that either.

The actual data file has many zero lines and I just want to pick up the lines that are with differences in a single export to Excel.

Thanks anyone for helping with this!

Re: How to filter out zero rows within an expression field (in Qlikview)?

sunny_talwar — Thu, 09 Nov 2017 23:32:57 GMT

May be try this

Dimension

Row

Expression

If(Report1Expression - Report2Expression <> 0, Report1Expression)

If(Report1Expression - Report2Expression <> 0, Report2Expression)

Report1Expression - Report2Expression

The reason we have to use if statement here is because we have to force all expressions to be 0 or null for the rows where difference is null so that those rows get removed from the chart

Re: How to filter out zero rows within an expression field (in Qlikview)?

joeluqlik — Fri, 10 Nov 2017 00:48:35 GMT

Hi Sunny,

Thanks for the quick response and guide here.

Are we saying it is a nested IF statement or simple a single statement like this will work?

If(R1_Amt - R2_Amt <> 0, R1_Amt - R2_Amt)

The R1_Amt is just an amount from one file, same as the R2_Amt is another amount from another file.

I am just trying to display only the non-zero difference lines in a straight table.

Re: How to filter out zero rows within an expression field (in Qlikview)?

sunny_talwar — Fri, 10 Nov 2017 13:05:49 GMT

I think you would need three expressions with one dimension

If(R1_Amt - R2_Amt <> 0, R1_Amt )

If(R1_Amt - R2_Amt <> 0, R2_Amt)

If(R1_Amt - R2_Amt <> 0, R1_Amt - R2_Amt)

Re: How to filter out zero rows within an expression field (in Qlikview)?

greend21 — Fri, 10 Nov 2017 15:21:52 GMT

Maybe it is not reading the result as a number for some reason. You could try wrapping the expression in Num(). You made sure to click your expression on the presentation tab and select Suppress Zero-Values for THAT field?

Re: How to filter out zero rows within an expression field (in Qlikview)?

joeluqlik — Fri, 10 Nov 2017 23:47:30 GMT

There is syntax error. Missing any comma or ()?

Re: How to filter out zero rows within an expression field (in Qlikview)?

joeluqlik — Sun, 12 Nov 2017 20:28:01 GMT

How do you create three expressions within one dimension?

Also the zero rows that I try to remove from my straight table are within the expression column, not within a dimension.

Re: How to filter out zero rows within an expression field (in Qlikview)?

sunny_talwar — Mon, 13 Nov 2017 12:04:34 GMT

Would you be able to share your application?