Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

anson_kong
New Contributor II

Remove the first record in load script

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 namedatedatetimeidlocation
17Jan04.xls2017-1-42017-1-4 12:30ID00123HA
17Jan05.xls2017-1-42017-1-4 12:30ID00123KL

how can do it in load statement?

thank you

1 Solution

Accepted Solutions

Re: Remove the first record in load script

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;

11 Replies
gerry_hdm
Contributor II

Re: Remove the first record in load script

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
New Contributor II

Re: Remove the first record in load script

thank you gerry_hdm

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

sureshqv
Esteemed Contributor III

Re: Remove the first record in load script

try may be

=firstsortedvalue(date,-ExcelFileName)

Re: Remove the first record in load script

Could you share the sample app/script ?

anson_kong
New Contributor II

Re: Remove the first record in load script

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

excel file namedatedatetimeidlocation
17Jan04.xls2017-1-42017-1-4 12:30ID00123HA

Re: Remove the first record in load script

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
Contributor II

Re: Remove the first record in load script

drop the table  or rename the Variable 

from your synthetic key

or  sending  a example in the Forum

gruß Gerold

sureshqv
Esteemed Contributor III

Re: Remove the first record in load script

try this with your data

Table1:

first 1

load * from table order by fieldx desc;

anson_kong
New Contributor II

Re: Remove the first record in load script

excel file namedatedatetimeidlocation
17Jan05.xls2017-1-42017-1-4 12:30ID00123KL

Yes, thank you

Community Browser