Remove the first record in load script

[anson_kong]

hi all,

I have the many excel from the supplier.

the excel stores the date, date time and ID for each day.

however , the ID may exists again in next day for revised record.

I would like to delete the first record and keep the latest one.

example.

excel file name	date	datetime	id	location
17Jan04.xls	2017-1-4	2017-1-4 12:30	ID00123	HA
17Jan05.xls	2017-1-4	2017-1-4 12:30	ID00123	KL

how can do it in load statement?

thank you

[avinashelite · Accepted solution]

Try like this :

Master:

LOAD

[excel file name],

date,

datetime,

id,

location

from

table

Right join

LOAD

max(datetime) as datetime,

id

resident

Master

Group by

id;

[anson_kong]

beautiful,

thank you avinashelite