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

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

amien — Tue, 11 Aug 2009 13:20:52 GMT

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 — Wed, 12 Aug 2009 00:15:48 GMT

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?

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

hector — Wed, 12 Aug 2009 00:44:14 GMT

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

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

johnw — Wed, 12 Aug 2009 01:00:55 GMT

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;

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

amien — Wed, 12 Aug 2009 02:01:55 GMT

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")

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

johnw — Wed, 12 Aug 2009 02:09:50 GMT

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?

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

amien — Wed, 12 Aug 2009 02:31:02 GMT

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'

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

hector — Wed, 12 Aug 2009 02:35:45 GMT

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

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

amien — Wed, 12 Aug 2009 03:01:52 GMT

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

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

johnw — Wed, 12 Aug 2009 03:11:06 GMT

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;

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

hector — Wed, 12 Aug 2009 03:11:41 GMT

Sorry, i dont work with csv files, by I tried to sort one of this, and i got the same error, maybe QV is unable to order this files like the excel files too.

Can you try with a resident instead?

c ya

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

johnw — Wed, 12 Aug 2009 03:16:56 GMT

Ah, yeah, resident might work. Maybe like this?

[Data]:
LOAD
recno() as Recno
,@2 as PostalCode
,@4 as SalesYTD
FROM 1.CSV (txt, codepage is 1252, no labels, delimiter is ';', no eof)
;
LEFT JOIN ([Data])
LOAD
Recno
,if(PostalCode=peek(PostalCode),peek("Row Number")+1,1) as "Row Number"
ORDER BY PostalCode, SalesYTD DESC
;
DROP FIELD Recno
;

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

hector — Wed, 12 Aug 2009 03:40:55 GMT

you have a missing "resident/from" between "Row Number" and "ORDER BY...", maybe "resident Data"??

i dont know, but recno is a key??

c ya

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

johnw — Wed, 12 Aug 2009 04:40:16 GMT

I'm using recno() to build a unique key for every row, yes, since I don't know the data and whether or not each row already has a unique key that we could use instead. That then lets me left join on the key, then get rid of the key when I no longer need it. Yeah, I missed the resident, and you guessed correctly what it should be:

[Data]:
LOAD
recno() as Recno
,@2 as PostalCode
,@4 as SalesYTD
FROM 1.CSV (txt, codepage is 1252, no labels, delimiter is ';', no eof)
;
LEFT JOIN ([Data])
LOAD
Recno
,if(PostalCode=peek(PostalCode),peek("Row Number")+1,1) as "Row Number"
RESIDENT [Data]
ORDER BY PostalCode, SalesYTD DESC
;
DROP FIELD Recno
;

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

amien — Wed, 12 Aug 2009 13:22:32 GMT

now i have not error at the ORDER BY .. but i only have a 1 in the Row Number field ..

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

amien — Wed, 12 Aug 2009 15:47:14 GMT

another game .. what if i want to connect another file and load the data .. based on the same rownumber and postalcode?

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

johnw — Wed, 12 Aug 2009 23:37:21 GMT

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.

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

amien — Thu, 13 Aug 2009 04:39:07 GMT

Thanks for your time .. got this working!

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

amien — Thu, 13 Aug 2009 04:41:38 GMT

esha replied on 08-12-2009 7:39

please dont waste this threat for this crap .. i filled in a complain

Thanks all for the effort .. appreciated!

hope some more people can use this technic .. the problem is that i want to load the data directly from SQL server with first building a view in SQL.

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

Wed, 14 Mar 2012 12:26:41 GMT

Great, John. Helped me. Thanks a lot!