Rank

[#1]

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)

[sunny_talwar]

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

[#3]

please find attached qvw

[sunny_talwar]

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;

[#5]

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

[sunny_talwar]

Is this what you want?

[#7]

Thanks Sunny

[#8]

Hi Sunny

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

same for weekly rank also

please suggest

[sunny_talwar]

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

[#10]

Hi Sunny

the Monthly dates are Month end dates

and the weekly dates are Sunday Dates