topic Re: How to convert set analysis expression in to load script if condition in QlikView

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

vikasshana — Fri, 29 Jul 2022 14:02:43 GMT

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,

@PrashantSangle @sunny_talwar @tresB

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

PrashantSangle — Fri, 29 Jul 2022 13:51:44 GMT

Can you share sample data with required output?

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

vikasshana — Fri, 29 Jul 2022 14:02:23 GMT

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)

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

PrashantSangle — Fri, 29 Jul 2022 14:31:49 GMT

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

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

sidhiq91 — Fri, 29 Jul 2022 15:29:53 GMT

@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.

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

vikasshana — Tue, 02 Aug 2022 09:39:45 GMT

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)

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

vikasshana — Tue, 02 Aug 2022 10:30:29 GMT

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