topic Re: IF statement in Set Expression in QlikView

IF statement in Set Expression

Qlik_vic — Sat, 16 Nov 2024 21:04:44 GMT

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.

Re: IF statement in Set Expression

sunny_talwar — Wed, 10 Apr 2019 12:07:20 GMT

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

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

Re: IF statement in Set Expression

Qlik_vic — Wed, 10 Apr 2019 12:13:56 GMT

Unfortunately that didnt work either

Re: IF statement in Set Expression

sunny_talwar — Wed, 10 Apr 2019 12:21:04 GMT

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

Re: IF statement in Set Expression

David_Capan — Wed, 10 Apr 2019 21:43:11 GMT

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

Re: IF statement in Set Expression

Qlik_vic — Thu, 11 Apr 2019 14:28:16 GMT

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

Re: IF statement in Set Expression

David_Capan — Thu, 11 Apr 2019 14:45:16 GMT

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.

Re: IF statement in Set Expression

Qlik_vic — Fri, 12 Apr 2019 05:41:30 GMT

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

PrashantSangle — Fri, 12 Apr 2019 05:52:14 GMT

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

Re: IF statement in Set Expression

Qlik_vic — Mon, 15 Apr 2019 07:00:49 GMT

Hi,

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

Re: IF statement in Set Expression

sunny_talwar — Mon, 15 Apr 2019 11:28:51 GMT

Would you be able to share a sample where we can see the issue for 2017?