Set analyse - blank values

[jozisvk11]

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.

[sunny_talwar · Accepted solution]

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

[jonathandienst]

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.