How to avoid duplicate rows with scripts

[Marco_Car]

Good Morning,

I have a table like:

Request, modified on

a, 31/12/2018

a, 01/01/2019

a, 02/02/2019

b, 03/02/2019

b,10/02/2019

I just want to obtain a table with the last modified record.

Which can be the right script?

[sunny_talwar]

So, based on the input provided what is the output you are hoping to get from the script?

[Marco_Car]

Hi

The desiderata should be:

a, 02/02/2019

b,10/02/2019

Thank you

[sunny_talwar]

Here is one option

Table:
LOAD Request,
	 Date(Max([modified on])) as [modified on]
Group By Request;
LOAD * INLINE [
    Request, modified on
    a, 31/12/2018
    a, 01/01/2019
    a, 02/02/2019
    b, 03/02/2019
    b, 10/02/2019
];

[sunny_talwar · Accepted solution]

Here is another approach

Table:
LOAD * INLINE [
    Request, modified on
    a, 31/12/2018
    a, 01/01/2019
    a, 02/02/2019
    b, 03/02/2019
    b, 10/02/2019
];

Right Join (Table)
LOAD Request,
	 Max([modified on]) as [modified on]
Resident Table
Group By Request;

[amrinder]

Hi Marco,

Please go through the below script:

Raw:
Load * Inline[
Request,modified
a,31/12/2018
a,01/01/2019
a,02/02/2019
b,03/02/2019
b,10/02/2019
];

NoConcatenate
T1:
LOAD
Request,
Date(Date#(modified,'DD/MM/YYYY'),'DD/MM/YYYY') as modified
Resident Raw;

DROP Table Raw;

Inner Join
LOAD
Request,
Date(Max(Date#(modified,'DD/MM/YYYY')),'DD/MM/YYYY') as modified
Resident T1 Group By Request;

Hope it helps.