topic Re: ROW_NUMBER() OVER (PARTITION BY A,B,C ordered by x) in QlikView

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

muratakkemik — Fri, 09 Nov 2018 07:23:32 GMT

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

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

andrey_krylov — Fri, 09 Nov 2018 07:54:23 GMT

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;

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

muratakkemik — Fri, 09 Nov 2018 16:21:20 GMT

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

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

vishsaggi — Fri, 09 Nov 2018 16:44:55 GMT

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.

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

muratakkemik — Fri, 09 Nov 2018 16:48:38 GMT

Im using QVD file

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

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

vishsaggi — Fri, 09 Nov 2018 16:56:16 GMT

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

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

muratakkemik — Fri, 09 Nov 2018 17:03:17 GMT

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

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

andrey_krylov — Fri, 09 Nov 2018 17:05:00 GMT

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.

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

muratakkemik — Fri, 09 Nov 2018 17:10:13 GMT

Perfect!

it works in 10 sec

Thank you

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

andrey_krylov — Wed, 14 Nov 2018 04:59:04 GMT

Unexpectedly! But I'm glad it helped))

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

kalyandg — Thu, 19 Sep 2019 04:17:50 GMT

HI,

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

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

Abhi999 — Wed, 15 Nov 2023 07:50:27 GMT

I tried this getting error message RN column not available .

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

tetew89 — Sat, 09 Mar 2024 15:49:00 GMT

Hi Andrey, I was trying to replicate this code but for me it is not working. Instead of EANdate I have the column Growth that I want to rank for. I see that you only put the EANDate in the order by clause. If I do the same with growth, it does not work. Do you know why?
Here my script
load *,
If(Previous(RelativeMonth)=RelativeMonth, Peek(Rank)+1, 1)
as Rank
resident tmp_ca
where SOUnits_PYTD <> 0
order by RelativeMonth, Code3, Year, Month, Growth