Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
thank you gerry_hdm
although it can find out the last value, synthetic key is created and cannot remove the first value.
try may be
=firstsortedvalue(date,-ExcelFileName)
Could you share the sample app/script ?
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 |
Kong, in your example jan 5 will be latest rite ? I didn't get how your identifying the Jan 4th as the latest
drop the table or rename the Variable
from your synthetic key
or sending a example in the Forum
gruß Gerold
try this with your data
Table1:
first 1
load * from table order by fieldx desc;
excel file name | date | datetime | id | location |
---|---|---|---|---|
17Jan05.xls | 2017-1-4 | 2017-1-4 12:30 | ID00123 | KL |
Yes, thank you