topic Re: Help with Set Expression in QlikView

Help with Set Expression

raajeshn — Tue, 31 Oct 2017 06:48:00 GMT

Hi Experts,

Request your help on the below scenario. Data set being used is as follows.

LOAD * INLINE [

KPI, Year, PKey, Value

Actual, 2017, 1, 30

Actual, 2016, 1, 40

Actual, 2014, 1, 50

Benchmark, , 1, 25

Actual, 2017, 2, 40

Actual, 2016, 2, 20

Actual, 2015, 2, 10

Benchmark, , 2, 40

];

..and the expected output is Sum of Benchmark based on the Actual for a Specific Year. For Ex, for Year 2016 - Expected Sum of Benchmark is 65 and expected sum of Benchmark for 2015 is 40 (25 is not counted as we dont have actual for 2015). Full Expected table is as below:

Year	Actual	Expected Benchmark
2014	50	25
2015	10	40
2016	60	65
2017	70	65

Sample file is attached where I have attempted the above but no luck. Any help is highly appreciated.

Thanks & Regards,

Raajesh N

Re: Help with Set Expression

arvind_patil — Tue, 31 Oct 2017 07:17:15 GMT

Hi Raajesh,

Please find the attachment.

Code Is as Below:

Data:

LOAD * INLINE [

KPI, Year, PKey, Value

Actual, 2017, 1, 30

Actual, 2016, 1, 40

Actual, 2014, 1, 50

Benchmark, , 1, 25

Actual, 2017, 2, 40

Actual, 2016, 2, 20

Actual, 2015, 2, 10

Benchmark, , 2, 40

];

NoConcatenate

FinalData:

LOAD KPI,Year,PKey,Value Resident Data where WildMatch(KPI,'Actual');

Left join(FinalData)

Master:

LOAD Distinct PKey,Sum(Value) as Benchmark Resident Data where WildMatch(KPI,'Benchmark') Group by PKey ;

DROP Table Data;

Thanks,

Arvind Patil

Re: Help with Set Expression

antoniotiman — Tue, 31 Oct 2017 07:21:58 GMT

Hi,

add this to Your Script

Left Join
LOAD PKey,Value as ValueB
Resident Table
Where KPI = 'Benchmark';

See Attachment.

Re: Help with Set Expression

raajeshn — Tue, 31 Oct 2017 07:30:28 GMT

Hi Arvind,

Thanks for your help and quick revert.

Unfortunately, this will not work as I cannot modify the data model (I am playing support role) and modifying the data model will be out of scope for now. Hence, trying to attempt this with the set expression. Thanks for your help.

Thanks & Regards,

Raajesh N

Re: Help with Set Expression

raajeshn — Tue, 31 Oct 2017 07:31:55 GMT

Hi Antonio,

Thanks for your help and quick revert.

Thanks & Regards,

Raajesh N

Re: Help with Set Expression

tresB — Tue, 31 Oct 2017 08:03:14 GMT

Try exp:

Sum(Aggr(NODISTINCT Sum( TOTAL {<KPI={'Benchmark'}>} Value), PKey))

Re: Help with Set Expression

arvind_patil — Tue, 31 Oct 2017 08:56:49 GMT

Hi Raajesh,

Then use below : tresesco code:

Sum(Aggr(NODISTINCT Sum( TOTAL {<KPI={'Benchmark'}>} Value), PKey))

Thanks,

Arvind Patil

Re: Help with Set Expression

raajeshn — Tue, 31 Oct 2017 13:59:39 GMT

Hi Tresesco,

Thanks much for the swift response.

Expression worked almost, except that it was showing slightly different numbers. However, I took a cue from your expression and modified it as below which works like a charm. Corrected Expression is as follows,

Sum(Aggr(NODISTINCT Sum({<KPI={'Benchmark'}>} Value), PKey))

Thanks & Regards,

Raajesh N

Re: Help with Set Expression

raajeshn — Tue, 31 Oct 2017 14:04:47 GMT

Hi Arvind,

Thanks, as mentioned in the above post for tresesco‌, slightly modified version works (which I also see in your attachment , though you have typed the expression from Tresesco as it is.

Thanks for your help.

Thanks & Regards,

Raajesh N