topic Case when in QV in QlikView

Case when in QV

Anonymous — Wed, 12 Dec 2012 08:08:13 GMT

I am trying to create a formula in a pivot table where I use case when and <> and LIKE

Something like:

Count distinct(case when [UOM for FROM_QTY.] <> 'RC' and TO_LOCATION LIKE '2%' then to_lic, else 0 end)

Have been looking at different treads and I came up with this:

=count({$<[UOM for FROM_QTY.]={"*"} - {'RC'}>}{$<TO_LOCATION={'2*'}>} distinct TO_LIC)

Also I need the counterpart of this one, so I tried:

=count({$<[UOM for FROM_QTY.]={'RC'}>}{$<TO_LOCATION={'2*'}>} distinct TO_LIC)

But that does not seem to give what I need,

They end up having the same count.

Any suggestions on why or how this is best solved?

Dan

Anonymous — Wed, 12 Dec 2012 08:14:27 GMT

Tried these two as well

=COUNT(DISTINCT if(not([UOM for FROM_QTY.] like 'RC') and TO_LOCATION LIKE '2*',TO_LIC,0))

=COUNT(DISTINCT if(([UOM for FROM_QTY.] like 'RC') and TO_LOCATION LIKE '2*',TO_LIC,0))

I now get different figures between these two, but not the fiugres I expected.

Dan

nirav_bhimani — Wed, 12 Dec 2012 10:06:24 GMT

HI,

In the if condition we never use else part. Remove that part.

Try to use - sign before = so that it will work like exclude.

=count({$<[UOM for FROM_QTY.] -={'RC'}>}{$<TO_LOCATION={'2*'}>} distinct TO_LIC)

Hope this will help.

try same syntax with P() - E () using set analysis.

Regards,

Nirav Bhimani