topic Re: How to avoid duplicate rows with scripts in App Development

How to avoid duplicate rows with scripts

Marco_Car — Fri, 29 Mar 2019 10:47:36 GMT

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?

Re: How to avoid duplicate rows with scripts

sunny_talwar — Fri, 29 Mar 2019 11:20:29 GMT

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

Re: How to avoid duplicate rows with scripts

Marco_Car — Fri, 29 Mar 2019 11:30:59 GMT

The desiderata should be:

a, 02/02/2019

b,10/02/2019

Thank you

Re: How to avoid duplicate rows with scripts

sunny_talwar — Fri, 29 Mar 2019 11:38:30 GMT

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
];

Re: How to avoid duplicate rows with scripts

sunny_talwar — Fri, 29 Mar 2019 11:39:38 GMT

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;

Re: How to avoid duplicate rows with scripts

amrinder — Fri, 29 Mar 2019 11:52:25 GMT

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.