Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
muratakkemik
Contributor III
Contributor III

ROW_NUMBER() OVER (PARTITION BY A,B,C ordered by x)

Hi All,

I have Sql query but i need to convert it to QV script. This (ROW_NUMBER() OVER (PARTITION BY A,B,C ordered by x)) function is not working in QV.

Here is my sql script:

; with cte as

(

select ah.artikelid, ae.eankod, s.EanColor, f.EanSize, ae.EanDate,

ROW_NUMBER() OVER (PARTITION BY ah.artikelid, s.EanColor, f.EanSize, ,ae.eankod,

ORDER BY ae.EanDate DESC) AS RN

from artikelhuvud ah inner join artikelean ae on ah.artikellopnr = ae.artikellopnr

inner join  farg f on ae.fargid = f.fargid inner join storlek s on s.storleksid = ae.storleksid

--where ah.artikelid = 223221

order by ah.artikelid,  s.EanColor, f.EanSize, ae.eankod

)

select * from cte

where RN=1

This is my QV script which needs to change;

EanCode:
load ARTIKELLOPNR, ARTIKELID,  EANColor,  EANSize, EANKOD, EANdate
resident EanCodeT
order by ARTIKELLOPNR, ARTIKELID,  EANColor,  EANSize, EANdate DESC;

Drop table EanCodeT;

Data like this:

ARTIKELLOPNR, ARTIKELID,  EANColor,  EANSize, EANKOD, EANdate,     RN   (This was coming with sql but need to be done in qv)

111                         2222               Black          L               12345     10-31-2018          1

111                         2222               Black          L                23232     10-20-2018          2

111                         2222               White          L                45454     10-25-2018          1

111                         2222               White          L                55585     10-20-2018          2

111                         2222               White          M               97474     09-21-2018          1

111                         2222               White          M               58585     09-21-2018          2

nearly Every item, color, size have 2 Ean codes I want to sort them date base and use their last one

Thank you

M

10 Replies
kalyandg
Partner
Partner

HI,

 

Can you please provide the solution you have tried, which works fast.