Issue with running consecutive Autonumber() for rank

[dafnis14]

Hi,

I am trying to use autonumber for ranking two different scores. Since I had an issue where rank numbers were missing,

I divided the scripts in 2 parts.

When commenting one of them the rank is fine.

But when executing the two scripts in a row, even if tables are dropped, some ranks are missing:

It looks like the autonumber is ranking values from the two scripts even though tables are deleted.

Attached is the model.

I will appreciate some guidelines for resolving this.

Thanks!

[sunny_talwar · Accepted solution]

Can you try running with these changes

RankScore7:

LOAD Key_Qtr_ParamId_7,

SIM_ParamOrder,

SIM_Score_7,

AutoNumber(SIM_Score_7, Key_Qtr_ParamId_7&'AutoNumber1') as SIM_Rank_Score_7

Resident SIM_Data

Order By Key_Qtr_ParamId_7, SIM_Score_7 desc;

and

RankScore6_7:

LOAD Key_Qtr_ParamId_6_7,

SIM_ParamOrder,

SIM_Scores_6_7,

AutoNumber(SIM_Scores_6_7, Key_Qtr_ParamId_6_7&'AutoNumber2') as SIM_Rank_Score_6_7

Resident SIM_Data

Order By Key_Qtr_ParamId_6_7, SIM_Scores_6_7 desc;

[dafnis14]

Sunny,

As usual, you solved the problem...

Can you please explain this strange  behavior?

It is not a very intuitive solution. Is there a bug with this function?

Thanks!

[sunny_talwar]

Difficult to explain, but the second parameter is an id which needs to be distinct for each distinct autonumber list that you are trying to create. But the issue in your case was that if the value of Key_Qtr_ParamId_6_7 and Key_Qtr_ParamId_7 are same the AutoNumber will assume it to be the part of same list. In order to differentiate them, I added a text field which would always make them different.

[dafnis14]

Thanks for elaborating.

But as I mentioned, I dropped the tables before I ran the second AutoNumber() script.

So this is very strange and unexpected..

[sunny_talwar]

I don't think that matters, during a single reload a single id will always create a continuous list of numbers even if they come from different tables or if one of the tables get dropped.

[dafnis14]

Ok...

Some things you learn only the hard way... I sure will remember that lesson 🙂

Thank you!

[thi_pham]

I got the same issues and been struggled till found out this post, thanks