Need help to load first N orders for each datestmp

[shahafei2]

I'm loading QVD that similar to this one:

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

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

for example:

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 15:00   | 2017  |  Jun       | 01   |  Friday       | 15     | 1

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

[vishsaggi]

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);

[vinieme12]

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;

[shahafei2]

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

[Anil_Babu_Samineni]

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

[vinieme12]

Which is exactly why we've used  Autonumber()

Try the script first and see

[shahafei2]

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?

[vinieme12]

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

[shahafei2]

Thank you I'll try it

[shahafei2]

It worked thank you