Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
ruchibagaria
Creator
Creator

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

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

13 Replies
sunny_talwar

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;

ruchibagaria
Creator
Creator
Author

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

sunny_talwar

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;

ruchibagaria
Creator
Creator
Author

Thank you so much