Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

jozisvk11
Contributor

Set analyse - blank values

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.

1 Solution

Accepted Solutions

Re: Set analyse - blank values

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...

2 Replies

Re: Set analyse - blank values

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...

MVP
MVP

Re: Set analyse - blank values

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein