topic Re: Set analyse - blank values in QlikView

Set analyse - blank values

jozisvk11 — Tue, 28 Mar 2017 06:51:21 GMT

Hello,

I have problem with set analyse. My set analyse is :

-1 * Sum ({< [ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396", "501398", "501399", "501400", "501401", "501588"}, [Object] = {'whinr110'},

[ID cost division] ={'701', '702', '703'} >}[Bill out of])

Everything works, but I have also some accounting records, where "ID cost division" is empty and I need them in my choice.. How can I choose in Set analyse also records, where is ID cost division unwritten.

Thank you.

Re: Set analyse - blank values

sunny_talwar — Tue, 28 Mar 2017 09:00:16 GMT

May be create a flag in the script

LOAD [ID cost division],

If(Len(Trim([ID cost division])) = 0 or Match([ID cost division], 701, 702, 703), 1, 0) as Flag

....

and then this

-1 * Sum ({< [ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396", "501398", "501399", "501400", "501401", "501588"}, [Object] = {'whinr110'},

Flag = {1}>}[Bill out of])

You can bring all the conditions into the script if you want...

Re: Set analyse - blank values

jonathandienst — Tue, 28 Mar 2017 09:48:37 GMT

In general, I would create a flag as Sunny suggests. But of for some reason you prefer not to change the load script (or cannot), then you may be able to do this in the front end.

If "empty" means an empty string ('') then use:

-1 * Sum ({<

[ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396", "501398", "501399", "501400", "501401", "501588"},

[Object] = {'whinr110'},

[ID cost division] ={'701', '702', '703', ''}

>} [Bill out of])

But I suspect by "empty" you mean NULL. Then try like this

-1 * Sum ({

<[ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396", "501398", "501399", "501400", "501401", "501588"},

[Object] = {'whinr110'}>

* ((1 - <[ID cost division]= {'*'}>) + <[ID cost division]={'701', '702', '703'})>)

} [Bill out of])

The highlighted expression takes all possible value and deducts all he cost divisions that have content. It then adds the three explicit cost divisions.