Get latest plan

paulwalker — Sat, 16 Nov 2024 00:56:56 GMT

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!

Re: Get latest plan

Taoufiq_Zarra — Mon, 30 Mar 2020 11:26:38 GMT

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;

topic Get latest plan in QlikView

Get latest plan

Re: Get latest plan