topic Re: Rank Help in QlikView

Rank Help

ljames88 — Thu, 04 May 2017 12:33:34 GMT

I have the following issue,

I am trying to rank how many times someone has ordered from us by date, so the first order would be 1, second would be 2, etc.with the following load statement. What I am trying to create is a last shipment date for every order by using ranking to autonumber.

Autonumber(Num(ShipDate), PATID_DRUG_COMBO) as Rank

However it is not ranking in the load statement is not working properly, When i rank in an expression it comes up with the correct rank, but I need it in the load statement.

Any suggestions on what I am doing wrong in the load.

When you see the attached you can see the ranking is wrong, Dec2016 should be 1, then Mar 2, April 6 should be 3 and april 12 should be 4.

Any help is much appreciated

Re: Rank Help

Frank_Hartmann — Thu, 04 May 2017 12:53:35 GMT

try adding:

RowNo() as Rank

in script and then sort your table by "Load Order" in case that your dates are consecutive.

If not, then please upload some sample data.

hope this helps

Re: Rank Help

sunny_talwar — Thu, 04 May 2017 13:09:51 GMT

I think this seems to be a sorting issue... you can try like this

Fact:

LOAD ShipDate,

PATID_DRUG_COMBO,

OtherFields....

FROM ....;

Left Join (Fact)

LOAD ShipDate,

PATID_DRUG_COMBO,

Autonumber(Num(ShipDate), PATID_DRUG_COMBO) as Rank

Resident Fact

Order By ShipDate;

Re: Rank Help

tresB — Thu, 04 May 2017 13:33:51 GMT

Perhaps the other dimension also would be required in the order by clause to rank them accordingly.

LOAD ShipDate,

PATID_DRUG_COMBO,

Autonumber(ShipDate, PATID_DRUG_COMBO) as Rank

Resident Fact

Order By PATID_DRUG_COMBO, ShipDate;

We can also remove the redundant num()

Re: Rank Help

sunny_talwar — Thu, 04 May 2017 13:35:59 GMT

Does it really matter to sort by PATID_DRUG_COMBO? For some reason I don't think we need it... but before I can say it confidently, I am going to perform a quick test

Re: Rank Help

tresB — Thu, 04 May 2017 13:40:44 GMT

Actually, including second dimension in the autonumber() made me think so.

Otherwise, the same could probably be got in the front end using =Rank(DateField)

Re: Rank Help

sunny_talwar — Thu, 04 May 2017 13:41:22 GMT

Here check it out

Table:

LOAD * INLINE [

PATID_DRUG_COMBO, ShipDate

ABC, 04/12/2017

ABC, 04/06/2017

ABC, 03/21/2017

DEF, 02/24/2017

ABC, 12/19/2016

DEF, 05/23/2017

ABC, 11/10/2016

];

Left Join (Table)

LOAD *,

Autonumber(Num(ShipDate), PATID_DRUG_COMBO) as Rank1

Resident Table

Order By ShipDate;

Left Join (Table)

LOAD *,

Autonumber(Num(ShipDate), PATID_DRUG_COMBO) as Rank2

Resident Table

Order By PATID_DRUG_COMBO, ShipDate;

Rank1 and Rank2 are the exact same

Re: Rank Help

tresB — Thu, 04 May 2017 13:55:42 GMT

Yes, you are right. Since the second dimension is there in autonumber(), it's order is actually being considered, so it it not necessary in the Order By.

Re: Rank Help

sunny_talwar — Thu, 04 May 2017 15:24:03 GMT

Yup