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

[gerry_hdm]

you kann use  2.  load statement

with lastvalue()  or   firstvalue()

in the 2.  loadscript

with Group and sort Statements

see here 

load
ID ,
// YTRAUF.LHMNR & max(#DATUM)as %LASTKEY,
  LastValue([excel file name]) as #LAST_Filename
  resident EXCEL
group by ID
order by ID ,[excel file Name];

grüße gerold 

[anson_kong]

thank you gerry_hdm

although it can find out the last value, synthetic key is created and cannot remove the first value.

[Chanty4u]

try may be

=firstsortedvalue(date,-ExcelFileName)

[avinashelite]

Could you share the sample app/script ?

[anson_kong]

I would like to have the below result, thank you all

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

[avinashelite]

Kong, in your example jan 5 will be latest rite ? I didn't get how your identifying the Jan 4th as the latest

[gerry_hdm]

drop the table  or rename the Variable 

from your synthetic key

or  sending  a example in the Forum

gruß Gerold

[Chanty4u]

try this with your data

Table1:

first 1

load * from table order by fieldx desc;

[anson_kong]

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

Yes, thank you