QlikView App Dev

[raajeshn]

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

[arvind_patil]

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

[antoniotiman]

Hi,

add this to Your Script

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

See Attachment.

[raajeshn]

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

[raajeshn]

Hi Antonio,

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

[tresesco]

Try exp:

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

[arvind_patil · Accepted solution]

Hi Raajesh,

Then use  below : tresesco code:

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

Thanks,

Arvind Patil

[raajeshn]

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

[raajeshn]

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