topic Re: ranking in QlikView

ranking

Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

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

Id | monthly | date.

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

Re: ranking

sunny_talwar — Thu, 08 Dec 2016 13:00:06 GMT

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

Re: ranking

Thu, 08 Dec 2016 13:22:30 GMT

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

Re: ranking

Anonymous — Thu, 08 Dec 2016 13:52:31 GMT

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;

Re: ranking

Anonymous — Thu, 08 Dec 2016 13:56:46 GMT

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.

Re: ranking

Anonymous — Thu, 08 Dec 2016 15:05:05 GMT

Hi,

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

Best regards,

Cosmina

Re: ranking

Thu, 08 Dec 2016 15:53:58 GMT

Hi Cosmina

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

please suggest

Re: ranking

Anonymous — Thu, 08 Dec 2016 16:03:25 GMT

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

Re: ranking

Thu, 08 Dec 2016 16:08:30 GMT

because the latest month should be with flag1 1 and

Re: ranking

MarcoWedel — Fri, 09 Dec 2016 00:21:49 GMT

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

Re: ranking

Anonymous — Fri, 09 Dec 2016 07:17:09 GMT

Hi,

Then use this

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;

Best regards,

Cosmina

Re: ranking

Fri, 09 Dec 2016 10:38:07 GMT

hi Marco

but it generating synth tables, is there a way to eliminate it

please suggest

Re: ranking

MarcoWedel — Sat, 10 Dec 2016 00:09:28 GMT

You could recombine the tables created by the generic load like this:

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'Attributes.*') THEN
  LEFT JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

But then again, maybe this legendary thread and blog post help as well to answer the question if this kind of synthetic keys should be avoided at all:

Should We Stop Worrying and Love the Synthetic Key?

Qlik Design Blog ‌

regards

Marco