Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikUser555
Contributor II
Contributor II

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

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. 

1 Solution

Accepted Solutions
QlikUser555
Contributor II
Contributor II
Author

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

View solution in original post

3 Replies
Arthur_Fong
Partner
Partner

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
MVP & Luminary
MVP & Luminary

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
Contributor II
Contributor II
Author

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

View solution in original post