topic Re: Need help to load first N orders for each datestmp in QlikView

Need help to load first N orders for each datestmp

shahafei2 — Wed, 25 Nov 2020 16:16:04 GMT

I'm loading QVD that similar to this one:

01/01/2017 14:00 | 2017 | Jun | 01 | Friday | 14 | 1

01/01/2017 14:00 | 2017 | Jun | 01 | Friday | 14 | 2

01/01/2017 14:00 | 2017 | Jun | 01 | Friday | 14 | 3

01/01/2017 14:00 | 2017 | Jun | 01 | Friday | 14 | 4

01/01/2017 15:00 | 2017 | Jun | 01 | Friday | 15 | 1

01/01/2017 15:00 | 2017 | Jun | 01 | Friday | 15 | 2

01/01/2017 15:00 | 2017 | Jun | 01 | Friday | 15 | 3

01/01/2017 15:00 | 2017 | Jun | 01 | Friday | 15 | 4

how could i load first 2(Dynamic Number) orders for each datestmp

for example:

01/01/2017 14:00 | 2017 | Jun | 01 | Friday | 14 | 1

01/01/2017 14:00 | 2017 | Jun | 01 | Friday | 14 | 2

01/01/2017 15:00 | 2017 | Jun | 01 | Friday | 15 | 1

01/01/2017 15:00 | 2017 | Jun | 01 | Friday | 15 | 2

Re: Need help to load first N orders for each datestmp

vishsaggi — Thu, 20 Apr 2017 20:01:30 GMT

What is your Load script can you share? And you mean you dont want to get ordernumber 3 and 4?

If yes, then use like:

LOAD *

FROM yourqvdpath\qvdname.qvd(qvd)

WHERE Not Match(OrderNumber, 3, 4);

Re: Need help to load first N orders for each datestmp

vinieme12 — Thu, 20 Apr 2017 20:09:59 GMT

try below

temp:

LOAD *,AutoNumber(Datestamp&OrderNumber,Datestamp) as RECGROUP inline [

Datestamp , Year , Month , Day , WeekDay , Hour , OrderNumber

01/01/2017 14:00 , 2017 , Jun , 01 , Friday , 14 , 1

01/01/2017 14:00 , 2017 , Jun , 01 , Friday , 14 , 2

01/01/2017 14:00 , 2017 , Jun , 01 , Friday , 14 , 3

01/01/2017 14:00 , 2017 , Jun , 01 , Friday , 14 , 4

01/01/2017 15:00 , 2017 , Jun , 01 , Friday , 15 , 1

01/01/2017 15:00 , 2017 , Jun , 01 , Friday , 15 , 2

01/01/2017 15:00 , 2017 , Jun , 01 , Friday , 15 , 3

01/01/2017 15:00 , 2017 , Jun , 01 , Friday , 15 , 4

];

NoConcatenate

FACT:

load * Resident temp

Where RECGROUP <=2;

Drop table temp;

Re: Need help to load first N orders for each datestmp

shahafei2 — Fri, 21 Apr 2017 04:05:20 GMT

My Problem it's that each ordernumber have differnt INT

(4430776534,654219876 and not 1,2,3,4)

i have more than 1M orders and the relevant information is the first 400

so i can't use the where functaion for <=400

Re: Need help to load first N orders for each datestmp

Anil_Babu_Samineni — Fri, 21 Apr 2017 05:19:23 GMT

Even if you have 1 M records Autonumber can fetch only 2 order numbers. Have you tried atleast which Vineet provides

If you tried, Can you provide live data

Re: Need help to load first N orders for each datestmp

vinieme12 — Fri, 21 Apr 2017 05:26:26 GMT

Which is exactly why we've used Autonumber()

Try the script first and see

Re: Need help to load first N orders for each datestmp

shahafei2 — Fri, 21 Apr 2017 05:33:02 GMT

I didn't understand how to create inline per 1M orders

as much as I know when I'm creating inline I have to enter the data itself

could i use existing fields from previous table?

If I understood it right ,could I use RowNu() function that will reset itself each time that the date stamp field will changed ? and than I believe I could use it (Where RowNo<=400)?

If so how could I force to reset the RowNo() each time the script recognize new date stamp?

Re: Need help to load first N orders for each datestmp

vinieme12 — Fri, 21 Apr 2017 05:48:45 GMT

That inline load was just to show you how it works

Your actual script would be like below

Temp:

Load

Autonumber (datestamp&order no, datestamp) as Recgroup

From yoursourcexxxxx;

NOCONCATENATE

FACT:

load * Resident temp

Where Recgroup <=n;

Drop Table temp;

This will load first n records for each datestamp

Re: Need help to load first N orders for each datestmp

shahafei2 — Fri, 21 Apr 2017 05:52:50 GMT

Thank you I'll try it

Re: Need help to load first N orders for each datestmp

shahafei2 — Sat, 22 Apr 2017 16:28:54 GMT

It worked thank you