topic Re: Group by at load script? in QlikView

Group by at load script?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Expert,

How to apply group by based on ID Column (Article), i used below script and getting repeated Article ID at output window.please advise me on the joining these below tables.

TotalNetPrice:

load SUM(TotalNetPrice) as TotalSales Resident Sales;

let vtotal=Peek('TotalSales');

TRACE vtotal=$(vtotal);

Cumulative%:

Load

RangeSum(peek(rum_sum),(TotalNetPrice/'$(vtotal)')) as run_sum

Resident Sales order by TotalNetPrice desc;

let vCum%=Peek('run_sum');

TRACE vCum%=$(vCum);

Grade:

load *,

if( ('$(vCum%)')<0.6,'A Stocks', if ( ('$(vCum%)')>0.6 and ('$(vtotal)')<0.9,'B Stocks','C Stocks') ) as Grade Resident Sales;

DROP Table Sales;

Re: Group by at load script?

Anonymous — Tue, 18 Oct 2016 18:04:44 GMT

See if this is:

Re: Group by at load script?

devarasu07 — Tue, 18 Oct 2016 18:46:28 GMT

Hi Marcio,

Thanks for your prompt reply, I just test your attached document and still it gives the repeated value and Cumulative sum showing wrong now. any suggestion/advise please.

Expected result based on Qlikview load script method,

Output1: detailed level

Output2: Summary level (by grade)

Re: Group by at load script?

Anonymous — Tue, 18 Oct 2016 19:19:29 GMT

I moved the object to a table Dynamic Graph.

See an improvement:

Re: Group by at load script?

devarasu07 — Tue, 18 Oct 2016 20:16:53 GMT

All credit goes to Sunny T

Attached document working fine and my issue resolved. Regards, Deva