sort records while loading

[#1]

My question is like this.

below is my original data

    type, action, date, customer

    Repair, Re001, 33, customer001

    Install, In001, 30, customer001

    Repair, Re002, 43, customer001   

    Install, In002, 30, customer002

    Repair, Re002, 39, customer002

I just want to load them all and have another "sort order" field and my result should be like this below. I mean group by customer and order by date.

type	action	customer	sort order	date
Install	In001	customer001	1	30
Repair	Re001	customer001	2	33
Repair	Re002	customer001	3	43
Install	In002	customer002	1	30
Repair	Re002	customer002	2	39

how can I write the script.

[danansell42]

Hi

Have you tried the following?

Load

     Type,

     Action,

     Date,

     Customer,

     RecNo() as SortOrder

from Table...

Order by Customer, Date asc;

Thanks

Dan

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD * Inline [

type, action, date, customer

    Repair, Re001, 33, customer001

    Install, In001, 30, customer001

    Repair, Re002, 43, customer001 

    Install, In002, 30, customer002

    Repair, Re002, 39, customer002

];

FinalTable:

LOAD *,

  AutoNumber(RecNo(), customer) as [sort order]

Resident Table

Order By customer, date;

DROP Table Table;

[squeakie_pig]

Can only do an order by on a Resident Load.

[danansell42]

Apologies, Yes you are correct.

In my test i did do a resident load however when i typed it here for some reason i removed that element for ease of reading which was obviously not the best thing to do 😕

Temp:

LOAD * INLINE [

    Type, Action, Date, Customer

    Repair,  Re001, 33,  customer001

    Install,  In001, 30,  customer001

    Repair,  Re002, 43,  customer001  

    Install,  In002, 30,  customer002

    Repair,  Re002, 39,  customer002

];

Test:

Load *,

  RecNo() as SortOrder

resident Temp

Order by Customer, Date asc;

drop Table Temp;

[#6]

it's not working.  the result will be

wotype	action	customer	date	SortOrder	I want
Install	In001	customer001	30	1	1
Repair	Re001	customer001	33	2	2
Repair	Re002	customer001	43	3	3
Install	In002	customer002	30	4	1
Repair	Re002	customer002	39	5	2

[#7]

I actually tried this way. quite the same as your answer. thank you.

tmp:

LOAD * INLINE [

    wotype, action, date, customer

    Repair, Re001, 33, customer001

    Install, In001, 30, customer001

    Repair, Re002, 43, customer001   

    Install, In002, 30, customer002

    Repair, Re002, 39, customer002

];

NoConcatenate

tmp_1:

load AutoNumber(date,customer),customer,date,action,wotype

Resident tmp

order by customer,date;