Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
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.
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?
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
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;
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")
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?
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'
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
i keep getting 'garbage after statement' .. the output is fine btw! 🙂
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;