Creating of filter with: if then

[beck_bakytbek]

Hi Folks, i got a situation where i have the following data (table)

Project, Projecttyp, Field1, Field2, Field3, Field4, NewField_Filter

A, Machine, 0, 1, 1 ,0, open

B, Upgrade, 1,1,1,0, closed

C, Upgrade,0,1,1,1, cancelled

my expected output is: new Field: NewField_Filter: here i am using the function: if..then to create this field:

if(Projecttyp = 'Machine' and Field1 = 0 and Field2 > 0 and Field3 > 0 and Field4 = 0 then open 

if(Projecttyp = 'Upgrade' and Field1 > 0 and Field2 > 0 and Field3 > 0 and Field4 = 0 then closed

if(Projecttyp = 'Upgrade' and Field1 > 0 and Field2 > 0 and Field3 > 0 and Field4  > 0 then cancelled

i am using these settings to create the new field, but i didn't achieve the any positive results. Does anybody have idea how to resolve this issue? Are there new / another methods to resolve this issue?

Thanks a lot in advance

Bek

[Oliver_F]

Hi,

you are mixing up the if as a script control statement ( https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptControlS... ) with the if script and chart expression ( https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalFun... ) .

You will have to use a nested if like:

if(Projecttyp = 'Machine' and Field1 = 0 and Field2 > 0 and Field3 > 0 and Field4 = 0,'open',

if(Projecttyp = 'Upgrade' and Field1 > 0 and Field2 > 0 and Field3 > 0 and Field4 = 0,'closed',

if(Projecttyp = 'Upgrade' and Field1 > 0 and Field2 > 0 and Field3 > 0 and Field4  > 0,'cancelled','none of the three conditions is true'))) as NewField_Filter

[beck_bakytbek]

Hi Oliver, thanks a lot for your time and feedback,

i used the above coding, but i dont have the expected values

[Oliver_F]

Hi,

so then your conditions must be wrong? What values do you get instead of the expected values?

At least for Project C I an say, that the Condition for Field1 > 0 is not fullfilled in the source data that you provided so either your source data or your condition is wrong.

[beck_bakytbek]

Hi Oliver,

thanks a lot for your time and help, when i am using the above coding, i receive only the values: cancelled and close, but the value: open doesn't appear

[Oliver_F]

Hi

then your data is different to the data that you have posted here. I copy pasted your data and my solution into a script and loaded the data.

The result is as expected:

A=open

B=closed

C=none of the three conditions is true

[beck_bakytbek]

Hi Olver, regarding the value: C i shoud have here status: cancelled, why do you have here status: none of the three conditions is true.

Thanks a lot for your help and time

[Oliver_F]

I have explained that earlier: "At least for Project C I an say, that the Condition for Field1 > 0 is not fullfilled in the source data that you provided so either your source data or your condition is wrong."

[beck_bakytbek]

i will check my data and will immediately notify you