RowNo() in combination with "order by value asc" creats second row

[QlikUser555]

Hello,

I have a Problem with RowNo() in Combination with "ORDER by value asc" in the load script. Everytime, when I call this two functions, my table gets two entries for every row. If I use only one function there is no problem, but if I first use "Order by" and than load the table again and call RowNo() the problem is there again.

Load *, RowNo() as DEFAULT_RANK Resident OVERALL_1 where not IsNull(DEFAULT_INDEX) ORDER BY DEFAULT_INDEX asc;

The Data Looks like this:

ID          DEFAULT_INDEX

43            0,3

123         0,009

21            0,5

23           0,12

I want to have a Table like this:

ID          DEFAULT_INDEX       DEFAULT RANK

123         0,009                                        1

23           0,12                                           2

43            0,3                                             3

21            0,5                                             4

And I get this Table:

ID          DEFAULT_INDEX       DEFAULT RANK

123         0,009                                        1

123         0,009                                        2

23           0,12                                           3

23           0,12                                           4

43            0,3                                             5

43            0,3                                             6

21            0,5                                             7

21            0,5                                             8

Where is my problem, I can't finde it. 

[Arthur_Fong]

Try to order the table first,

then resident that table and add in RowNo() into the table.

Remember to drop the original table to avoid synthetic keys.

[marcus_sommer]

I don't think that there is anything wrong with the mentioned features - single or combined - else that the data are in this way and/or that you load the data twice. Just check if the following approach changed the results:

noconcatenate Load *, RowNo() as DEFAULT_RANK
Resident OVERALL_1 where not IsNull(DEFAULT_INDEX) ORDER BY DEFAULT_INDEX asc;
drop tables OVERALL_1;

and if not the add a recno() to the first load which creates OVERALL_1 to check the data-source.

- Marcus

[QlikUser555 · Accepted solution]

Thank you for your anwsers,

I had a problem with the reverse engineering finding the error. 

I did make a outer join bevor, but had no key value in both tables common, so it did a internal cross table. The error was not visibel until I call the RowNo() function. The Problem with the "oder by" I couldn't reproduce.

Greetings,

Tobias