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

1 Solution

Accepted Solutions
andrey_krylov
Specialist
Specialist

Another option

EanCode:
load ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANKOD, EANdate

,If(Previous(ARTIKELLOPNR) = ARTIKELLOPNR and Previous(ARTIKELID) = ARTIKELID and Previous(EANColor) = EANColor and Previous(EANSize) = EANSize, Peek(RN) + 1, 1) as RN
resident EanCodeT
order by ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANdate DESC;

but I'm not sure it will be faster although if you try and share the result  it might be interesting.

View solution in original post

10 Replies
andrey_krylov
Specialist
Specialist

Hi Murat, try AutoNumber(RowNo()

EanCode:
load ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANKOD, EANdate

,AutoNumber(RowNo(), ARTIKELLOPNR & ARTIKELID & EANColor & EANSize) as RN
resident EanCodeT
order by ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANdate DESC;

muratakkemik
Contributor III
Contributor III
Author

Thank you for your reply, this solution (AutoNumber) is working so slow. I have more than 2m records and it takes nearly 3 hours. is there any another way to do that?

thx

vishsaggi
Champion III
Champion III

Are you running Andrey's script from SQL or using QVD? Like i mean run you sql script and store it in your QVD.

Then load your qvd and then use Autonumber Andrey suggested. Check that way and let us know.

muratakkemik
Contributor III
Contributor III
Author

Im using QVD file

as you see it took already 30min for half, and still working

Screen Shot 2018-11-09 at 11.46.44 AM.png

vishsaggi
Champion III
Champion III

Can you share the script you are running for EanCodeT table? Are you using Resident or straight from Qvd?

muratakkemik
Contributor III
Contributor III
Author

First of all taking data from QVD into cash then using that script in resident and still it is working

andrey_krylov
Specialist
Specialist

Another option

EanCode:
load ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANKOD, EANdate

,If(Previous(ARTIKELLOPNR) = ARTIKELLOPNR and Previous(ARTIKELID) = ARTIKELID and Previous(EANColor) = EANColor and Previous(EANSize) = EANSize, Peek(RN) + 1, 1) as RN
resident EanCodeT
order by ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANdate DESC;

but I'm not sure it will be faster although if you try and share the result  it might be interesting.

View solution in original post

muratakkemik
Contributor III
Contributor III
Author

Perfect!

it works in 10 sec

Thank you

andrey_krylov
Specialist
Specialist

Unexpectedly! But I'm glad it helped))