Find latest row for each "Kenteken" and only import those

[Mitch_Data]

Hi there.

I have a problem and I can't seem to fix it in my load editor I want to import only the values of the latest row found for each Kenteken. So for instance a Kenteken xx-00-xx can be on row 12000 and the same one xx-00-xx can be on 14000. I only want to import the latest row with that particular kenteken and other fields in order to get rid off duplicates. I want it to be done in the load editor and not in the front-end.

Unfortunately I can't do anything with dates because they can be similar and there are too many variances.

This is the script:

feit_Boekwaardelijst:

Load DISTINCT

     Kenteken,

    "Boekwaarde bedrag",

    "Boekwaarde datum",

"Boekwaarde bedrag BTW",

"Restwaarde bedrag",

    "Restwaarde datum",

"Afschrijving termijnen",

"Afschrijving KM",

  "Afschrijving schade",

"Afschrijving kosten",

"Afschrijving corr. rente",

Verkoopbedrag,

"Kostprijs verkoop",

"Verkoop resultaat",

Investering,

  "Verkoopprijs incl. BTW",

  Capitalize("Debiteur verkoop") as  "Debiteur verkoop",

  '-' & "Debiteur verkoop" as Handelaren,

    "Verkoop soort",

  Num("Inname stand", '#.##0') as "Inname stand"

FROM [lib://IC Files - Company/Stream - ML - Algemeen/App - Verkooprapportage/Boekwaardelijst 2018 - 2023.xlsx]

(ooxml, embedded labels, table is Tabelle1;

Thanks a lot.

[BrunPierre]

Hi, could you share a few lines of the dataset.

[Mitch_Data]

Sure thing. This is an example how it may look. Unfortunately date can't be used since sometimes the date can be the same for 4 instances for example. In this case I want to take row number 3 and not import number 2, because the a higher row number means that it is being imported later. 

Of course the dataset consists of multiple 100.000 rows.

[BrunPierre]

Try

Data:
LOAD AutoNumber(Recno(),Kenteken) as %Key
*
FROM Source;

Inner Join
LOAD Max(%Key) as %Key
Resident Data;