

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Tags:
- qlikview_scripting
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Im using QVD file
as you see it took already 30min for half, and still working


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you share the script you are running for EanCodeT table? Are you using Resident or straight from Qvd?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
First of all taking data from QVD into cash then using that script in resident and still it is working


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Perfect!
it works in 10 sec
Thank you


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Unexpectedly! But I'm glad it helped))

- « Previous Replies
-
- 1
- 2
- Next Replies »