topic Re: Issue with running consecutive Autonumber() for rank in QlikView

Issue with running consecutive Autonumber() for rank

dafnis14 — Wed, 31 Aug 2016 08:25:13 GMT

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!

Re: Issue with running consecutive Autonumber() for rank

sunny_talwar — Wed, 31 Aug 2016 08:29:02 GMT

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;

Re: Issue with running consecutive Autonumber() for rank

dafnis14 — Wed, 31 Aug 2016 08:44:40 GMT

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!

Re: Issue with running consecutive Autonumber() for rank

sunny_talwar — Wed, 31 Aug 2016 08:49:29 GMT

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.

Re: Issue with running consecutive Autonumber() for rank

dafnis14 — Wed, 31 Aug 2016 08:59:56 GMT

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..

Re: Issue with running consecutive Autonumber() for rank

sunny_talwar — Wed, 31 Aug 2016 09:02:12 GMT

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.

Re: Issue with running consecutive Autonumber() for rank

dafnis14 — Wed, 31 Aug 2016 09:06:23 GMT

Ok...

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

Thank you!

Re: Issue with running consecutive Autonumber() for rank

thi_pham — Thu, 18 Mar 2021 06:13:41 GMT

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