topic Re: Set analysis equivalent in load script (rank and aggr in script) in QlikView

Set analysis equivalent in load script (rank and aggr in script)

Anonymous — Wed, 19 Oct 2016 03:08:52 GMT

I have used rank function and aggr in frontend which is making me app very slow. Please advice how can i do this in the backend instead.

=sum(aggr(sum(Aggr( if(Rank(W_Period)>4 and rank(W_Period) <= SIT_Lead ,sum( WS_Qty)),Market,[Planning Article],Article,W_Period)),Market,[Planning Article],Article))

The above expression does a sum of WS_Qty over a range of W_Period (4 to a particulat number SIT_Lead) and it is being aggregated over three fields Market, planning article and article.

Regards,

Ruchi

Re: Set analysis equivalent in load script (rank and aggr in script)

sunny_talwar — Wed, 19 Oct 2016 03:21:42 GMT

May be something like this:

Aggr_Table_Temp:

LOAD Market,

[Planning Article],

Article,

W_Period,

SIT_Lead,

RowNo() as Rank,

Sum(WS_QTY) as Sum_WS_QTY

Resident FactTable

Order By W_Period Desc

Group By Market, [Planning Article], Article, W_Period, SIT_Lead;

Aggr_Table:

LOAD Market,

[Planning Article],

Article,

Sum(If(Rank > 4 and Rank <= SIT_Lead, Sum_WS_QTY) as Sum_WS_QTY

Resident Aggr_Table_Temp

Group By Market, [Planning Article], Article;

Re: Set analysis equivalent in load script (rank and aggr in script)

Anonymous — Wed, 19 Oct 2016 05:34:41 GMT

Hi Sunny,

Thanks for the reposnse. The rowno() wouldnt work here as its a crosstable data. I tried implelementing this code but the rank nos are too high.

Regards.

Ruchi

Re: Set analysis equivalent in load script (rank and aggr in script)

sunny_talwar — Wed, 19 Oct 2016 08:53:51 GMT

Can you share some sample data with expected output or your application to see what you are doing? I gave you a general script because I don't know the specific, may be if you can share the specifics, I can help you better

Re: Set analysis equivalent in load script (rank and aggr in script)

Anonymous — Wed, 19 Oct 2016 09:23:25 GMT

Hi Sunny,

Thank you.

PFA a sample. Required output is to find sum of qty of each country over a period. This period is maximum fourth position of Period to SIT_Lead position in Period. this has to be aggregated over Market,[Planning Article],Article.

=sum(aggr(sum(Aggr( if(Rank(W_Period)>4 and rank(W_Period) <= SIT_Lead ,sum( WS_Qty)),Market,[Planning Article],Article,W_Period)),Market,[Planning Article],Article))

Regards,

Ruchi

Re: Set analysis equivalent in load script (rank and aggr in script)

sunny_talwar — Wed, 19 Oct 2016 09:56:12 GMT

See if the attached is what you wanted

Re: Set analysis equivalent in load script (rank and aggr in script)

Anonymous — Tue, 06 Dec 2016 09:03:17 GMT

Hi Sunny,

Sorry for not replying on this post. I worked on it now. Although the soluntion works when i use sample data, but when I use actual data, the Autonumber just goes on and on to reload. Is there any other alternavtive? Thank you.

Regards,

Ruchi

Re: Set analysis equivalent in load script (rank and aggr in script)

sunny_talwar — Tue, 06 Dec 2016 10:12:58 GMT

Would you be able to share the script you have used for your actual data?

Re: Set analysis equivalent in load script (rank and aggr in script)

Anonymous — Wed, 07 Dec 2016 04:52:09 GMT

Hi Sunny,

Here is the script:

FactTable:

LOAD Distinct Market,

[Planning Article],

Article,

Period,

W_Period,

WS_Period,

RWFlag,

SIT_Lead,

WS_Qty,

WS_GBP

FROM

[..\Source Files\QVD\FINAL_FACTS_DATA.qvd]

(qvd) where RWFlag ='Qty';

Aggr_Table_Temp:

LOAD Distinct Market,

[Planning Article],

Article,

W_Period, !

Period,

SIT_Lead,

AutoNumber(RowNo(), Market&[Planning Article]&Article&Period) as Rank,

Sum(WS_Qty) as Sum_WS_Qty,

Sum(WS_GBP) as Sum_WS_GBP

Resident FactTable

Group By Market, [Planning Article], Article, W_Period, SIT_Lead,Period

Order By W_Period Desc;

Please note that this autonumber with so many fields is causing the problem. I tried taking in separate loads, making the fields combo as key outside and using that directly, calculating rowno outside etc. but no use.

And I need to have these many fileds here. Please advice.

Regards,

Ruchi

Re: Set analysis equivalent in load script (rank and aggr in script)

Anonymous — Wed, 07 Dec 2016 08:28:38 GMT

Hi Sunny,

I think I got it. I used fewer fields in the key and then left joined it with the main fact table. Looks okay now. I shall update if its not working. And thank you for replying even on an old post. 🙂

Regards,

Ruchi

Re: Set analysis equivalent in load script (rank and aggr in script)

sunny_talwar — Wed, 07 Dec 2016 09:49:03 GMT

I have heard AutoNumber to be slow, so I am going to give you an alternative just in case you want to give it a try

Aggr_Table_Temp:

LOAD Market,

[Planning Article],

Article,

W_Period,

SIT_Lead,

AutoNumber(RowNo(), Market&[Planning Article]&Article) as Rank,

If(Previous(Market) = Market and Previous([Planning Article]) = [Planning Article] and Previous(Article) = Article, RangeSum(Peek('Rank'), 1), 1) as Rank1

Sum(WS_Qty) as Sum_WS_Qty

Resident FactTable

Group By Market, [Planning Article], Article, W_Period, SIT_Lead

Order By Market, [Planning Article], Article, W_Period Desc;

Re: Set analysis equivalent in load script (rank and aggr in script)

Anonymous — Wed, 07 Dec 2016 11:37:42 GMT

Hi Sunny,

Thanks once again. Sorry but I didnt understand this. Here, anyway we have to do autonumber to do a peek on the Rank. Sorry if I am missing something in my understanding.

Regards,

Ruchi

Re: Set analysis equivalent in load script (rank and aggr in script)

sunny_talwar — Wed, 07 Dec 2016 12:08:11 GMT

Well I left the AutoNumber for comparison between Rank and Rank1 field. You can remove the script which uses AutoNumber and try with the new Rank

Aggr_Table_Temp:

LOAD Market,

[Planning Article],

Article,

W_Period,

SIT_Lead,

If(Previous(Market) = Market and Previous([Planning Article]) = [Planning Article] and Previous(Article) = Article, RangeSum(Peek('Rank'), 1), 1) as Rank

Sum(WS_Qty) as Sum_WS_Qty

Resident FactTable

Group By Market, [Planning Article], Article, W_Period, SIT_Lead

Order By Market, [Planning Article], Article, W_Period Desc;

Re: Set analysis equivalent in load script (rank and aggr in script)

Anonymous — Wed, 07 Dec 2016 15:41:53 GMT

Thank you so much