Get max date of each id

[udaya_kumar]

Hi,

Consider the below example

ID - Start date - End date - Value

1 - 01/02/2016 - 31/12/9999 -  10

1 - 01/02/2016 - 31/12/9999 -  20

1 - 01/05/2016 - 31/12/9999 -  20

2 - 01/02/2016 - 31/12/9999 -  10

2 - 01/03/2016 - 31/12/9999 -  20

2 - 01/04/2016 - 31/12/9999 -  20

Result :

ID - Start date - End date - Value

1 - 01/05/2016 - 31/12/9999 -  10

1 - 01/05/2016 - 31/12/9999 -  20

1 - 01/05/2016 - 31/12/9999 -  20

2 - 01/04/2016 - 31/12/9999 -  10

2 - 01/04/2016 - 31/12/9999 -  20

2 - 01/04/2016 - 31/12/9999 -  20

I want the max date of each ID 1,2,3,....

And assign it to other start dates of same ID in script.

How can we achieve this?

[sunny_talwar]

May be this:

Table:

LOAD *,

  RowNo() as Key

INLINE [

    ID - Start date - End date - Value

    1 - 01/02/2016 - 31/12/9999 -  10

    1 - 01/02/2016 - 31/12/9999 -  20

    1 - 01/05/2016 - 31/12/9999 -  20

    2 - 01/02/2016 - 31/12/9999 -  10

    2 - 01/03/2016 - 31/12/9999 -  20

    2 - 01/04/2016 - 31/12/9999 -  20

] (delimiter is '-');

FinalTable:

NoConcatenate

LOAD ID,

  Key,

  If(ID = Previous(ID), Peek('New_Start date'), [Start date]) as [New_Start date],

  [End date],

  Value

Resident Table

Order By ID, [Start date] desc;

DROP Table Table;

RENAME Field [New_Start date] to [Start date];

[swuehl]

Or another option:

SET DateFormat = 'DD/MM/YYYY';

INPUT:

LOAD * INLINE [

ID , Start date , End date , Value

1 , 01/02/2016 , 31/12/9999 ,  10

1 , 01/02/2016 , 31/12/9999 ,  20

1 , 01/05/2016 , 31/12/9999 ,  20

2 , 01/02/2016 , 31/12/9999 ,  10

2 , 01/03/2016 , 31/12/9999 ,  20

2 , 01/04/2016 , 31/12/9999 ,  20

];

LEFT JOIN

LOAD ID, Date(Max([Start date])) as NewStart

RESIDENT INPUT

GROUP BY ID;

[Clever_Anjos]

One possible solution (my 2 cents)

Base:
LOAD * INLINE [
    ID ,Start date ,End date ,Value
    1 ,01/02/2016 ,31/12/9999 , 10
    1 ,01/02/2016 ,31/12/9999 , 20
    1 ,01/05/2016 ,31/12/9999 , 20
    2 ,01/02/2016 ,31/12/9999 , 10
    2 ,01/03/2016 ,31/12/9999 , 20
    2 ,01/04/2016 ,31/12/9999 , 20
];
t:
Mapping LOAD
  ID
  ,Date(MAx([Start date])) as MaxDate
Resident Base
group By ID;

Final:
LOAD
  ID ,
  [Start date] ,
  [End date] ,
  Value,
  ApplyMap('t',ID) as [Max Date]
Resident Base;
Drop Table Base;

[Peter_Cammaert]

Three valid solutions that return the expected results, and now which one do you choose?

In case you have a large table to modify, you may want to do a performance test with every one of them.