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

[Anonymous]

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

[sunny_talwar]

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;

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

See if the attached is what you wanted

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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

[Anonymous]

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