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'

[hector]

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

[johnw]

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
;

[hector]

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

[johnw]

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
;

[amien]

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

[amien]

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

[johnw · Accepted solution]

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.

[amien]

Thanks for your time .. got this working!

[amien]

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.

[#11]

Great, John. Helped me. Thanks a lot!