topic Re: sort records while loading in QlikView

sort records while loading

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: sort records while loading

danansell42 — Fri, 05 Aug 2016 08:22:00 GMT

Have you tried the following?

Load

Type,

Action,

Date,

Customer,

RecNo() as SortOrder

from Table...

Order by Customer, Date asc;

Thanks

Dan

Re: sort records while loading

sunny_talwar — Fri, 05 Aug 2016 10:13:37 GMT

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;

Re: sort records while loading

squeakie_pig — Fri, 05 Aug 2016 10:36:24 GMT

Can only do an order by on a Resident Load.

Re: sort records while loading

danansell42 — Fri, 05 Aug 2016 11:24:14 GMT

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;

Re: sort records while loading

Mon, 15 Aug 2016 00:49:29 GMT

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

Re: sort records while loading

Mon, 15 Aug 2016 00:56:04 GMT

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;