36 Replies Latest reply: Sep 13, 2018 10:00 AM by Bilal Chaudhary

# Nested IF with AND operator in Qlik Sense

Hi everyone,

i have a problem when i try to create a new calculated field by using a Nested IF.

For example I have a Field Age and I want create a new Field (Color) with 'A' if Age > 10 and < 20, 'B' if Age >=20 and < 40 and so on...

I try to create this function but Qlik show me this error: missing right parenthesis:

 `if``(``Age` `> 10 and ``Age` `< 20,A,`

 `7` ` ``if``(``Age` `>= 20 and ``Age` `< 40,B,))`

Thanks,

Livio

• ###### Re: Nested IF with AND operator in Qlik Sense

Share full expression..

• ###### Re: Nested IF with AND operator in Qlik Sense

If(City Code> 100 and City Code <20, 'A', If(City Code >= 20 and City Code < 40, 'B'))

• ###### Re: Nested IF with AND operator in Qlik Sense

If([City Code] > 100 and [City Code]<20, 'A',

If([City Code] >= 20 and [City Code] < 40, 'B'))

• ###### Re: Nested IF with AND operator in Qlik Sense

Try to create like below

If(Age>100 and Age<20, 'A', If(Age>=20 and Age<40, 'B'))

• ###### Re: Nested IF with AND operator in Qlik Sense

Hi,

i have try to create another calculated field like this:

If(City Code> 100 and City Code <20, 'A', If(City Code >= 20 and City Code < 40, 'B'))

But Qlik show me the same error

• ###### Re: Nested IF with AND operator in Qlik Sense

True, Qlik consider as 2 fields which called appending. So, you could try [City Code] instead City Code

If([City Code]> 100 and [City Code] <20, 'A', If([City Code] >= 20 and [City Code] < 40, 'B'))

• ###### Re: Nested IF with AND operator in Qlik Sense

Hi,

I solve the problem by using the Data Loader but I don't undertand why it's no function using "Add Calculated Field" in Data Model

• ###### Re: Nested IF with AND operator in Qlik Sense

If..Else.. Available in Script and UI too. Where it not working for you? Can you show image at least?

• ###### Re: Nested IF with AND operator in Qlik Sense

Hi,

If i use the DATA MANAGER the result is:

By using Data Loader the script is ok and i'm able to create the calculated filed that you can see in the picture up.

• ###### Re: Nested IF with AND operator in Qlik Sense

Seems, Data manager also working. Where you facing the wrong values?

• ###### Re: Nested IF with AND operator in Qlik Sense

In the Data Manager like you can see in the picture

• ###### Re: Nested IF with AND operator in Qlik Sense

You may miss understanding the IF condition

If([City Code]> 10 and [City Code] <20, 'A', If([City Code] >= 20 and [City Code] < 40, 'B', 'C'))

Explanation - If [City Code] arbitrary between 10 and 20 then it returns only A and [City Code] arbitrary between 20 and 40 then it returns only B. That means, If [City Code] < 10 and [City Code] >40 it returns C only. The same way your functionality works

• ###### Re: Nested IF with AND operator in Qlik Sense

I'm sorry but if i use this script:

If([City Code]> 10 and [City Code] <20, 'A', If([City Code] >= 20 and [City Code] < 40, 'B', 'C'))

the result is ever wrong It's ok only if i use the data loader editor

• ###### Re: Nested IF with AND operator in Qlik Sense

That's impossible. Because, The functionality is same for Script / UI.

• ###### Re: Nested IF with AND operator in Qlik Sense

Remove last comma or put a else value, like:

`.....if``(``Age` `>= 20 and ``Age` `< 40,B))`

`OR`

`if(Age >= 20 and Age < 40,B, C))`

• ###### Re: Nested IF with AND operator in Qlik Sense

HI,

You seem to have forgotten the default value.

 `if``(``Age` `> 10 and ``Age` `< 20,'A',`

 `7` `if``(``Age` `>= 20 and ``Age` `< 40,'B',???))`

Filling up the ??? will help you. else remove the comma after B.

Hope this helps

• ###### Re: Nested IF with AND operator in Qlik Sense

No, nothing. Qlik show me the same error

• ###### Re: Nested IF with AND operator in Qlik Sense

Just for reference ...

if([Sale>=10 and Sale <=20   ,Green(),

if(Sale >20 ,Blue(),

if(Sale >0 and Sale <10 ,Black(),

rgb(255,0,0))) )

• ###### Re: Nested IF with AND operator in Qlik Sense

No, qlik show me the same error....

if(City Code>=10 and City Code <=20   ,Green(),

if(City Code >20 ,Blue(),

if(City Code >0 and City Code <10 ,Black(),

rgb(255,0,0))) )

• ###### Re: Nested IF with AND operator in Qlik Sense

If your field is called City Code, you have to use [City Code].

You should also name your column:

if(Stuff) as CityCodeColor

• ###### Re: Nested IF with AND operator in Qlik Sense

What error you are getting ...

If possible please share error screen or sample app.

• ###### Re: Nested IF with AND operator in Qlik Sense

or use [City Code]  instead of City Code

• ###### Re: Nested IF with AND operator in Qlik Sense

Hi, I try but in the Data Manager Qlik show me that missing right parenthesis. I don't understand

• ###### Re: Nested IF with AND operator in Qlik Sense

if(City Code>=10 and City Code <=20   ,Green(),

if(City Code >20 ,Blue(),

if(City Code >0 and City Code <10 ,Black(),

rgb(255,0,0))) )

• ###### Re: Nested IF with AND operator in Qlik Sense

No, I see the same error again

• ###### Re: Nested IF with AND operator in Qlik Sense

How i can share you the fiels?

• ###### Re: Nested IF with AND operator in Qlik Sense

Qlik send me this error if i try to upload the file:

You are not allowed to create or update this content

However i've a simple xls with a field "City Code", by using this i want to create a new field like "PROVA" with the script:

if ([City Code] > 10 AND [City Code] < 50, 'HELLO',

if([City Code] >= 50 AND [City Code] < 100, 'WORLD', 'OTHERS'))

prma7799

• ###### Re: Nested IF with AND operator in Qlik Sense

Your expression seems to be ok but without sample file how can I explain to or please  share excel file atleast.

• ###### Re: Nested IF with AND operator in Qlik Sense

Please share data don't attach screen shot..

• ###### Re: Nested IF with AND operator in Qlik Sense

I can't share, qlik community send me that i'm not authorized for to do this operation.

• ###### Re: Nested IF with AND operator in Qlik Sense

This worked!! thanks

• ###### Re: Nested IF with AND operator in Qlik Sense

Hi

We are facing this same issue, did you get the solution to this ? I don't understand the difference in your script & the one in "Correct Answer".

• ###### Re: Nested IF with AND operator in Qlik Sense

We've found a solution: So using the same example If([City Code]> 10 and [City Code] <20, 'A', If([City Code] >= 20 and [City Code] < 40, 'B', 'C'))

We changed it to If([City Code]< 10,'C', if([City Code] <20, 'A', If([City Code]< 40, 'B', 'C')))

This will give you the same result.