Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Qlik_vic
New Contributor

IF statement in Set Expression

Hi,

Is it possible to use IF statements in Set Variables?

If would like to do the following:

vSetExcludeItems:

If (year>=2018, ProductCode-={'44636'}, ProductCode-={'46488'})

But it doesnt work, so am I write it in the wrong way or is it not possible to do like this?

So I want to be able to use the vSetExcludeItems in charts and tables in a set expression, so that I do not have to do an if statement in all chart objects.

Labels (1)
10 Replies

Re: IF statement in Set Expression

This won't work like this, but may be something like this

{<year *= {">=2018"}, ProductCode -= {'44636'}>+<year *= {"<2018"}, ProductCode -= {'46488'}>}
Qlik_vic
New Contributor

Re: IF statement in Set Expression

Unfortunately that didnt work either

Re: IF statement in Set Expression

Would you be able to provide a sample where you are trying this?

Employee
Employee

Re: IF statement in Set Expression

Try making a flag in the script:
if( year >= 2018, if(ProductCode = '44636', 0,1), if(ProductCode = '46488', 0,1)) as Product_Flag
Then in your set analysis you could use {<Product_Flag = {1}>}
Qlik_vic
New Contributor

Re: IF statement in Set Expression

The fields are in separate tables, how should I do when the year comes from the common calendar and the product from product table?

Employee
Employee

Re: IF statement in Set Expression

The lookup function may work
if(Lookup('year','Date_Key',Date_Key,'CalendarTable') >= 2018, if(ProductCode = '44636', 0,1), if(ProductCode = '46488', 0,1)) as Product_Flag
Then in your set analysis you could use {<Product_Flag = {1}>}

 

Note:  You may have to perform a RESIDENT load to do this, assuming you're loading the calendar at the end of the script, as you can only look up values that have already been loaded.

Qlik_vic
New Contributor

Re: IF statement in Set Expression

Hi,

I cannot get it to work or then I just end up with loops in the tables. I can just make the flags in separate tables so I have one product flag and one year flag then of course I can make an union in the set expression, but then the problem is there are charts that need to have set variables as well in the expressions. Is there any way to combine more than two flags or flags and set variables?

Re: IF statement in Set Expression

how did you implement sunny's solutions . I think it should work.

1(using sunny solution):
Sum({<year = {">=2018"}, ProductCode -= {'44636'}>+<year *= {"<2018"}, ProductCode -= {'46488'}>}Sales)

2:If (year>=2018,Sum({<ProductCode-={'44636'}>}Sales), Sum({<ProductCode-={'46488'}>}Sales))
Great dreamer's dreams never fulfilled, they are always transcended.
Qlik_vic
New Contributor

Re: IF statement in Set Expression

Hi,

The solution Sunny provided gives the correct result for 2018, but for 2017 it shows both products.