Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Set analysis with empty values problem

Hello,

I use set analysis to filter data. When i try to put a filter in field that contains also empty values in order to exclude 2 values then also the empty values dissapear.

The expression i use is:

sum({< ORDER_StatusDesc= {"CO-Released", "REPLORD-Released"},PRDLI_Desc = {"Die Cut","Die Cut Glued"},MFO_StateDesc -= {"MFO Cancelled","MFO Finished"} >} ORDER_GrossSqm)

In the field MFO_StateDesc i have some records with empty value. When i put in the expression the "MFO_StateDesc -= {"MFO Cancelled","MFO Finished"}"  part then the MFO_Cancelled and MFO_Finished are removed but also the empty ones. I don 't want the empty to be removed.


Can any one help.


3 Replies
Highlighted

Re: Set analysis with empty values problem

How about this

Sum({<ORDER_StatusDesc= {"CO-Released", "REPLORD-Released"},PRDLI_Desc = {"Die Cut","Die Cut Glued"}, MFO_StateDesc = e({<MFO_StateDesc  = {"MFO Cancelled","MFO Finished"}>})>} ORDER_GrossSqm)

Highlighted
Contributor
Contributor

Re: Set analysis with empty values problem

Thanks for the reply.

Same effect. The empty values are lost.

Highlighted

Re: Set analysis with empty values problem

May be convert nulls to white spaces in the script...

If(IsNull(MFO_StateDesc), ' ', MFO_StateDesc) as MFO_StateDesc

Or you can create a new field for the purposes of this set analysis

If(Match(MFO_StateDesc, 'MFO Cancelled', 'MFO Finished'), 0, 1) as Flag

and then use this

Sum({<ORDER_StatusDesc= {"CO-Released", "REPLORD-Released"},PRDLI_Desc = {"Die Cut","Die Cut Glued"}, Flag = {1}>} ORDER_GrossSqm)