topic How to write an If logic in Load Script with multiple conditions in QlikView

How to write an If logic in Load Script with multiple conditions

vikasshana — Tue, 23 Aug 2022 10:12:57 GMT

Hi Experts,

I've the below example data.

candidate	Sales
Exec-123	100
Exec1-567	200
Exec2-890	300
Exec-123	400
Exec-123	500
Exec-123	600
Exec1-567	700
Exec1-567	800
Exec2-890	900
Exec2-890	1000
Test1	2000
Test2	3000

Expected Output

candidate	Sales
Exec1-567	3,300 (100+200+400+500+600+700+800)
Exec1-890	3,800 (100+300+400+500+600+900+1000)
Test1	2000
Test2	3000

Here we are adding Exec-123 values to Exec1-567 and Exec1-890

I've the below two set analysis created for it but I would like to it in load script.

=sum({<candidate={"*Exec*"}>}Sales)+sum({<candidate={"*Exec1*"}>}Sales)

=sum({<candidate={"*Exec2*"}>}Sales)+sum({<candidate={"*Exec1*"}>}Sales)

As a work around I did it by taking the resident load of the main table. But is there any if statement I can use directly in the main table rather than taking residents and calculating

Regards,

Barat Vikas

Re: How to write an If logic in Load Script with multiple conditions

Taoufiq_Zarra — Wed, 24 Aug 2022 09:26:21 GMT

@vikasshana

you can use this version in load script:

Data: LOAD *,if(candidate='Exec-123' or candidate='Exec1-567','Exec1-567',candidate) as candidate1 ,if(candidate='Exec-123' or candidate='Exec2-890','Exec2-890',candidate) as candidate2 INLINE [ candidate , Sales Exec-123 , 100 Exec1-567 , 200 Exec2-890 , 300 Exec-123 , 400 Exec-123 , 500 Exec-123 , 600 Exec1-567 , 700 Exec1-567 , 800 Exec2-890 , 900 Exec2-890 , 1000 Test1 , 2000 Test2 , 3000 ]; output: load candidate1 as candidate,sum(Sales) as Sales resident Data where candidate1='Exec1-567' group by candidate1 ; load candidate2 as candidate,sum(Sales) as Sales resident Data where candidate2='Exec2-890' group by candidate2 ; load candidate as candidate,sum(Sales) as Sales resident Data where Match(candidate,'Exec2-890','Exec1-567','Exec-123')=0 group by candidate ; drop table Data;

output:

dimension candidate and measure sum(Sales):

Re: How to write an If logic in Load Script with multiple conditions

oskartoivonen — Mon, 05 Sep 2022 06:45:46 GMT

You're essentially including specific fact rows in two different calculations. If you do this on the script side like @Taoufiq_Zarra solution suggests, you have to duplicate your fact rows, which is generally ill-advised, as your fact data is now not sum-able without de-duplication logic which adds generally unnecessary complexity to the frontend.