Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasshana
Creator II
Creator II

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

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

2 Replies
Taoufiq_Zarra

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

Taoufiq_Zarra_0-1661333176751.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
oskartoivonen
Partner - Contributor III
Partner - Contributor III

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.