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

[amien]

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'

[johnw]

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]

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]

Wait, that's SQL? How embarassing! 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]

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]

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]

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]

I've got this problem in sometimes

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]

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

[johnw]

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;