Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasshana
Creator II
Creator II

How to convert set analysis expression in to load script if condition

Hi Team,

I've the below two expression, I would like to do them in the load script instead of set expression. '*Exec*' is nothing but a wild match

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

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

Let me know how to achieve it.

Regards,

V

@PrashantSangle @sunny_talwar @tresesco 

1 Solution

Accepted Solutions
PrashantSangle

Try below

 

data:
Load *
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
];

NoConcatenate

Temp:
Load
Sum(Sales) as ex_Sales
Resident data
where not wildmatch(candidate,'*Exec1*','*Exec2*')
group by candidate
;
Join
Load
candidate,
Sum(Sales) as Sales
Resident data
where wildmatch(candidate,'*Exec1*','*Exec2*')
group by candidate
;
drop table data;

NoConcatenate
Final:
Load candidate,
Sum(ex_Sales + Sales) as Sales
Resident Temp
group by candidate
;

Drop table Temp;

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

6 Replies
PrashantSangle

Can you share sample data with required output?

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
vikasshana
Creator II
Creator II
Author

Hi @PrashantSangle ,

Below is the 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

 

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

Try below

 

data:
Load *
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
];

NoConcatenate

Temp:
Load
Sum(Sales) as ex_Sales
Resident data
where not wildmatch(candidate,'*Exec1*','*Exec2*')
group by candidate
;
Join
Load
candidate,
Sum(Sales) as Sales
Resident data
where wildmatch(candidate,'*Exec1*','*Exec2*')
group by candidate
;
drop table data;

NoConcatenate
Final:
Load candidate,
Sum(ex_Sales + Sales) as Sales
Resident Temp
group by candidate
;

Drop table Temp;

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sidhiq91
Specialist II
Specialist II

@vikasshana  I tried the below Code, 

NoConcatenate
Temp:
Load * 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
];

NoConcatenate
Temp1:
Load candidate,
Sum(Sales) as Total_Sales
Resident Temp
where not WildMatch(candidate,'*Exec1*','*Exec2*')
group by candidate;

Concatenate(Temp1)
Load candidate,
Sum(Sales) as Total_Sales
Resident Temp
where WildMatch(candidate,'*Exec1*','*Exec2*')
group by candidate;
Drop table Temp;

Exit Script;

Let me know if it is something that you are looking for.

vikasshana
Creator II
Creator II
Author

thank you @PrashantSangle and @sidhiq91 for the suggestion, but I would like to see output as below.

candidate Sales
Exec1-567 3,300 (100+200+400+500+600+700+800)
Exec1-890 3,800 (100+300+400+500+600+900+1000)
vikasshana
Creator II
Creator II
Author

Thanks for that @PrashantSangle , it is working as expected.