ranking

[#1]

Hi Experts,

I have the below field in a table it’s a combination of  3 fields

Id | monthly | date.

or 

id | weekly | date

I want to rank the date for Id | monthly | date. Separately as a new field called Month Rank

I want to rank the date for Id | weekly | date Separately as a new field called Week Rank

Pleases suggest how to do that

[sunny_talwar]

How are you ranking them? Is there a measure here also?

[#3]

hi sunny

i want to rank the dates with middle word monthly as a separate field

and dates with middle word monthly as a separate field

below in the xls is the exact format i am looking for

[Anonymous]

Fact:

LOAD A,

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

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

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

FROM

(ooxml, no labels, table is Sheet1);

Temp:

LOAD *, if(Peek(ID,-1)<>ID,1,Peek(Flag1,-1)+1) as Flag1

Resident Fact

order by FlagRank, ID, Date desc;

drop table Fact;

[Anonymous]

And you can use in the set analyses the flag FlagRank, to show in diffrent collumn, or you can separate Fact in 2 tables based on Flag and apply the same alghoritm in the script. I hope it helps.

[Anonymous · Accepted solution]

Hi,

If you need the columns in design mode, based on selection. I've made an application.

Best regards,

Cosmina

[#7]

Hi Cosmina

this is exactly what i am looking for but the field Flag1 value needs to be in descending order

please suggest

[Anonymous]

Hi,

The Flag1 has to be ascending, because the rank is using this field. As you can see in the dimension, the result is correct. Can you tell me why do you need as descending?

Best regards,

Cosmina

[#9]

hi

because the latest month should be  with flag1 1 and

[MarcoWedel]

Hi,

another solution might be:

table1:
Generic
LOAD SubField(A,'|',1) as ID,
    A,
    SubField(A,'|',2)&Pick(IterNo(),' date',' rank'),
    Pick(IterNo(),Date(Date#(SubField(A,'|',3),'YYYYMMDD')),AutoNumber(SubField(A,'|',3),Left(A,Index(A,'|',2))))
FROM [https://community.qlik.com/servlet/JiveServlet/download/1174373-256921/Book1.xlsx] (ooxml, no labels, table is Sheet1)
While IterNo()<=2;

hope this helps

regards

Marco