topic Re: Rank in QlikView

Rank

Wed, 25 Nov 2020 16:16:04 GMT

Hi All

Below is the calculations which i want to achieve in script

these calculated dimensions are working correctly but i need that in load script

please suggest

Weekly_Rank	=aggr(rank(sum({<Flag={"Weekly"}>}delta)),ID,Flag,delta)
Monthly_Rank	=aggr(rank(sum({<Flag={"Monthly"}>}delta)),ID,Flag,delta)
Live_Rank	=aggr(rank(sum({<Flag={"Live"}>}delta)),ID,Flag,delta)

Re: Rank

sunny_talwar — Fri, 16 Dec 2016 13:05:18 GMT

Do you have a qvw where you have already loaded this data?

Re: Rank

Fri, 16 Dec 2016 13:08:55 GMT

please find attached qvw

Re: Rank

sunny_talwar — Fri, 16 Dec 2016 13:22:59 GMT

This?

Fact:

LOAD A,B,

SubField(A,'|',1) as ID,

SubField(A,'|',2) as Flag,

date#(SubField(A,'|',3),'YYYYMMDD') as Date

FROM

[Book1 (1).xlsx]

(ooxml, table is Sheet2);

Temp1:

LOAD *,

if(Peek(ID,-1)<>ID,1,Peek(delta,-1)+1) as delta,

If(Flag = 'Weekly', If(Flag = Previous(Flag), if(Peek(ID,-1)<>ID,1,Peek(delta,-1)+1), 1)) as Weekly_Rank,

If(Flag = 'Monthly', If(Flag = Previous(Flag), if(Peek(ID,-1)<>ID,1,Peek(delta,-1)+1), 1)) as Monthly_Rank,

If(Flag = 'Live', If(Flag = Previous(Flag), if(Peek(ID,-1)<>ID,1,Peek(delta,-1)+1), 1)) as Live_Rank

Resident Fact

Order By Flag, ID, Date desc;

DROP Table Fact;

Re: Rank

Fri, 16 Dec 2016 13:51:08 GMT

Hi Sunny

Exactly this, and if i choose Monthly Rank =1

and Flag = Monthly

only one date to be selected, now its choosing two dates

Re: Rank

sunny_talwar — Fri, 16 Dec 2016 14:08:10 GMT

Is this what you want?

Re: Rank

Fri, 16 Dec 2016 15:00:56 GMT

Thanks Sunny

Re: Rank

Mon, 19 Dec 2016 10:06:16 GMT

Hi Sunny

the monthly rank is not grouping it by distinct Monthly Date field

same for weekly rank also

please suggest

Re: Rank

sunny_talwar — Mon, 19 Dec 2016 12:00:01 GMT

Are your date MonthStart or MonthEnd? If not, that might be causing the issue here

Re: Rank

Mon, 19 Dec 2016 14:26:38 GMT

Hi Sunny

the Monthly dates are Month end dates

and the weekly dates are Sunday Dates

Re: Rank

sunny_talwar — Mon, 19 Dec 2016 14:33:21 GMT

Are you using another script or the same exact script as in your sample?

Re: Rank

Mon, 19 Dec 2016 14:41:01 GMT

Sunny

I am using the same exact script as in the bok2.qvw

the actual problem is in the below screen shot

for monthly_rank 1 two dates are showing there should be only one date

Re: Rank

sunny_talwar — Mon, 19 Dec 2016 14:43:40 GMT

Can you post a snapshot of the issue you are having? I am struggling to understand the issue.

Re: Rank

Mon, 19 Dec 2016 15:02:13 GMT

Hi Sunny

this value in field a 120273012|monthly|20160930 should have a different Monthly_rank

i want the Monthly_rank to be grouped by month end dates

and the weekly_rank to be grouped by weekly dates

Re: Rank

sunny_talwar — Mon, 19 Dec 2016 15:16:53 GMT

But isn't this issue resolved in Book2_v1?

Re: Rank

Mon, 19 Dec 2016 15:24:37 GMT

Hi Sunny

in Book2_v1

if Monthy_Rank 1 and 2 Selected, same values repeating

Re: Rank

Anonymous — Mon, 19 Dec 2016 17:33:42 GMT

The values have to repeat--there are two different IDs for Date = 20161130.

Where are you getting Weekly_Rank, Monthly_Rank, and Live_Rank from? Your Peek() function looks for them in the Fact table loaded from your Excel workbook, but the Excel workbook has no such fields. I think you mean to use "B" in the Peek() function, right?