Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
vikasshana
Creator
Creator

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.

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.
vikasshana
Creator
Creator
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.
sidhiq91
Specialist
Specialist

@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
Creator
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
Creator
Author

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