Rank Help

[ljames88]

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

[Frank_Hartmann]

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

[sunny_talwar]

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;

[tresesco]

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()

[sunny_talwar]

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

[tresesco]

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)

[sunny_talwar]

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

[tresesco]

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.

[sunny_talwar]

Yup