topic Re: simple set anlaysis with WHERE clause in QlikView

simple set anlaysis with WHERE clause

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

Hi guys i need help with some set analysis

I want to =SUM(FUEL_MILES) where DRIVER is NULL

Fields:

FUEL_MILES

DRIVER

Re: simple set anlaysis with WHERE clause

jmvilaplanap — Tue, 04 Jul 2017 15:29:20 GMT

Hi, Try with this

sum({<Driver={''}>}FUEL_MILES)

Re: simple set anlaysis with WHERE clause

vishsaggi — Tue, 04 Jul 2017 15:30:39 GMT

Try this:

= SUM({< DRIVER = {"= Len(Trim(DRIVER)) = 0 "} >} FUEL_MILES)

Re: simple set anlaysis with WHERE clause

Anonymous — Tue, 04 Jul 2017 15:30:54 GMT

try

sum({$-<DRIVER={"*"}>} [FUEL_MILES9])

Re: simple set anlaysis with WHERE clause

Chernov — Tue, 04 Jul 2017 15:38:35 GMT

SUM( { < DRIVER -={'*'} > } FUEL_MILES)

-= : mean NOT

'*' : mean All values

Re: simple set anlaysis with WHERE clause

aaronnayan — Tue, 04 Jul 2017 15:40:01 GMT

I want this set analysis to calculate FUEL_MILES when there is no driver. (NULL)

Re: simple set anlaysis with WHERE clause

Anonymous — Tue, 04 Jul 2017 15:40:20 GMT

I think that won't work. You'll get the empty set.

Re: simple set anlaysis with WHERE clause

Anonymous — Tue, 04 Jul 2017 15:41:08 GMT

is your "NULL" null() or 'null' ?

Re: simple set anlaysis with WHERE clause

effinty2112 — Tue, 04 Jul 2017 15:42:37 GMT

Hi Aaron,

Maybe:

=sum(FUEL_MILES)-sum({$<Driver={"*"}>}FUEL_MILES)

Cheers

Andrew

Edit : Robin has written his expression in a far better way than mine. I think my formulation is easy to understand but once you do understand what's going on Robin's expression should be used.

Re: simple set anlaysis with WHERE clause

vishsaggi — Tue, 04 Jul 2017 15:47:13 GMT

Did you try any of the expressions suggested? How is your null represented? Like actual word Null or '-' hyphen?

Re: simple set anlaysis with WHERE clause

aaronnayan — Tue, 04 Jul 2017 16:06:02 GMT

Thankyou guys

For the help

Originaly it was Null in text

I made a change in the script so it would be easier in the future= IF(DRIVER>0,1,0) AS JO_DRIVER,

SUM( { < JO_HasDriver={0} > } FUEL_MILES)