topic Set Analysis syntax for excluding nulls in QlikView

Set Analysis syntax for excluding nulls

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I got a pivot that sums total spend amount for various FY and I would like to exclude nulls from Sub Rollup without adding this field as dimension. Is this the correct syntax?

Re: Set Analysis syntax for excluding nulls

sunny_talwar — Thu, 07 Jul 2016 20:45:34 GMT

Is it true null or does it says NULL? For true null, try one of these:

{<DRVD_PRD_SUB_ROLLUP -= {'*'},....

{<DRVD_PRD_SUB_ROLLUP = e({<DRVD_PRD_SUB_ROLLUP = {'*'}>}),....

{<DRVD_PRD_SUB_ROLLUP -= {"=Len(Trim(DRVD_PRD_SUB_ROLLUP )) > 0"},....

Re: Set Analysis syntax for excluding nulls

Thu, 07 Jul 2016 20:51:22 GMT

Thanks Sunny! I'll try all three. Btw, what is the "e" for in this example?

{<DRVD_PRD_SUB_ROLLUP = e({<DRVD_PRD_SUB_ROLLUP = {'*'}>}),....

Re: Set Analysis syntax for excluding nulls

sunny_talwar — Thu, 07 Jul 2016 20:58:12 GMT

I think, I might have made a mistake with the 1st two... try these

{<DRVD_PRD_SUB_ROLLUP = {'*'},....

{<DRVD_PRD_SUB_ROLLUP -= {"=Len(Trim(DRVD_PRD_SUB_ROLLUP )) > 0"},....

Re: Set Analysis syntax for excluding nulls

sunny_talwar — Thu, 07 Jul 2016 21:03:24 GMT

e means exclude and p mean possible. Look at the attached document for more details on e and p

Re: Set Analysis syntax for excluding nulls

Thu, 07 Jul 2016 21:05:48 GMT

So these two will exclude true null DRVD_PRD_SUB_ROLLUP ?

Re: Set Analysis syntax for excluding nulls

Thu, 07 Jul 2016 21:15:14 GMT

<DRVD_PRD_SUB_ROLLUP = {'*'},.... worked but the others did not.

Re: Set Analysis syntax for excluding nulls

sunny_talwar — Fri, 08 Jul 2016 01:21:39 GMT

I left the - negative sign by mistake... should have been this

{<DRVD_PRD_SUB_ROLLUP = {"=Len(Trim(DRVD_PRD_SUB_ROLLUP )) > 0"},....

Re: Set Analysis syntax for excluding nulls

Fri, 08 Jul 2016 13:00:52 GMT

Oh ok. Thanks!