Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
amien
Specialist
Specialist

ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number'

this is not possible in the edit screen right? i could solve this by first building a view in sql and then load the data from that view .. is this really not possible in Qlikview?

ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number'

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

OK, with all the mistakes I've made, I guess I'd better create a working example. OK, looks like I needed BOTH previous and peek, not one or the other. Example attached.

As for connecting another file based on the same rownumber and postalcode, that's going to depend on what your other file looks like.

View solution in original post

21 Replies
johnw
Champion III
Champion III

I don't recognize that syntax, so I can't tell what you're trying to do, so I can't tell you if it is possible. Can you state what you want to do in some other way, perhaps with some example data and desired result?

hector
Specialist
Specialist

Hi, maybe you can use something like this

Load

[Row Number ] as RowNumber

;

SQL

Select

ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number'

from table

;

Regards

johnw
Champion III
Champion III

Wait, that's SQL? How embarassing! Embarrassed I'm kind of the DB2 performance expert at our shop, though we're always using the oldest version still supported, so maybe that's my excuse for not recognizing it.

OK, now that I'm actually looking up ROW_NUMBER and OVER, I can see what you're trying to do. I'm not sure if I have this exactly right, but something like this should work:

LOAD
if(PostalCode=previous(PostalCode),previous("Row Number")+1,1) as "Row Number",
...
ORDER BY PostalCode, SalesYTD DESC;

amien
Specialist
Specialist
Author

Thanks for this reply .. sound like a great option .. but i'm getting an error .. Row Number field doenst exist .. problem here: previous("Row Number")

johnw
Champion III
Champion III

Maybe it's peek() instead of previous():

LOAD
if(PostalCode=peek(PostalCode),peek("Row Number")+1,1) as "Row Number",
...
ORDER BY PostalCode, SalesYTD DESC;

Does that work?

amien
Specialist
Specialist
Author

PEEK works .. but the ORDER doenst work .. is this because i'm using a CSV file?

LOAD @1 AS field1,
@3 AS field2,
@4 AS PostalCode,
IF(@4=PEEK(@4),PEEK("Row Number")+1,1) as "Row Number"
FROM 1.CSV (txt, codepage is 1252, no labels, delimiter is ';', no eof)
ORDER BY @4;

data is loaded. . hangs on ORDER BY .. Row Number only contains a '1'

hector
Specialist
Specialist

I've got this problem in sometimes Stick out tongue

First you have to sort, then apply the peek/previous function

something like this

Load

*,
IF(PostalCode=PEEK(PostalCode),PEEK("Row Number")+1,1) as "Row Number"

;
LOAD @1 AS field1,
@3 AS field2,
@4 AS PostalCode,
FROM 1.CSV (txt, codepage is 1252, no labels, delimiter is ';', no eof)
ORDER BY @4;

Good luck

amien
Specialist
Specialist
Author

i keep getting 'garbage after statement' .. the output is fine btw! 🙂

johnw
Champion III
Champion III

Need to remove a comma:

Load

*,
IF(PostalCode=PEEK(PostalCode),PEEK("Row Number")+1,1) as "Row Number"

;
LOAD @1 AS field1,
@3 AS field2,
@4 AS PostalCode // removed a comma here
FROM 1.CSV (txt, codepage is 1252, no labels, delimiter is ';', no eof)
ORDER BY @4;