Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jozisvk11
Creator
Creator

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
sunny_talwar

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

View solution in original post

2 Replies
sunny_talwar

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
Partner - Champion III
Partner - Champion III

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