Get latest plan

[paulwalker]

Hi Community,

I have to create the flag which will be the following case!

I have two plans that have the sane Insurance ID, and same current/Previous year.

Please note,  we need to show, which are the latest updated plan.

Example: for Insurance ID=BBB,

Output should be the   Case 2, because case 2 has latest updated one.

InsuranceID	StartDate	EndDate	Rate
AAA	Jan-19	Jan-20	100
AAA	Jan-19	Jan-20	200
AAA	Jan-19	Jan-20	240
AAA	Jan-20	Jan-21	2500
AAA	Jan-20	Jan-21	123
BBB	Jul-18	Jan-20	250
BBB	Jul-18	Jan-20	1100
BBB	Feb-19	Jun-20	1000
BBB	Feb-19	Jun-20	450

OutPut Should be:

Insurance	StartDate	EndDate	Rate
AAA	Jan-20	Jan-21	2500
AAA	Jan-20	Jan-21	123
BBB	Feb-19	Jun-20	1000
BBB	Feb-19	Jun-20	450

Thanks in Advance!

[Taoufiq_Zarra · Accepted solution]

Maye be :

Data:
LOAD Date(Date#(StartDate,'MMM-YY')) as ID, * INLINE [
    InsuranceID, StartDate, EndDate, Rate
    AAA, Jan-19, Jan-20, 100
    AAA, Jan-19, Jan-20, 200
    AAA, Jan-19, Jan-20, 240
    AAA, Jan-20, Jan-21, 2500
    AAA, Jan-20, Jan-21, 123
    BBB, Jul-18, Jan-20, 250
    BBB, Jul-18, Jan-20, 1100
    BBB, Feb-19, Jun-20, 1000
    BBB, Feb-19, Jun-20, 450
];
left join(Data)

load InsuranceID, Date(Max(ID)) as MaxDate resident Data group by InsuranceID;


output:
noconcatenate

load * resident Data where ID=MaxDate;

drop table Data;
drop fields MaxDate,ID;