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

[vikasshana]

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

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

[oskartoivonen]

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.