How to calculate EndDate base on startDate

[komminenianusha]

Hi All,

I have a requirement to calculate ENDDate based on StartDate.

Find below details for reference

This table is input data

Load * inline [
Product,Discount,StartDate
Shoes,10,01/01/2020
Shoes,20,01/03/2020
Shoes,40,01/06/2020
Shirt,20,01/09/2020
Shirt,30,01/10/2020
Shirt,60,01/11/2020
];

Expected Output:

  Load * inline [
Product,Discount,StartDate,EndDate
Shoes,10,01/01/2020,29/02/2020
Shoes,20,01/03/2020,31/05/2020
Shoes,40,01/06/2020,
Shirt,20,01/09/2020,30/09/2020
Shirt,30,01/10/2020,05/11/2020
Shirt,60,06/11/2020,
];

Please anyone help me to achieve this.

[PrashantSangle · Accepted solution]

try below code,

Temp:
Load Product,Discount,Date(Date#(StartDate,'DD/MM/YYYY')) as StartDate inline [
Product,Discount,StartDate
Shoes,10,01/01/2020
Shoes,20,01/03/2020
Shoes,40,01/06/2020
Shirt,20,01/09/2020
Shirt,30,01/10/2020
Shirt,60,06/11/2020,
];

NoConcatenate
Temp1:
Load *,
if(Previous(Product)<> Product,'-',Date(Previous(StartDate)-1)) as nextDate
Resident Temp
order by StartDate desc;

Drop table Temp;

Thanks & Regards,

Prashant Sangle

https://predoole.com/

[sunny_talwar]

@PrashantSangle - it might need to be sorted first by Product and the StartDate desc

NoConcatenate
Temp1:
LOAD *,
     If(Previous(Product) <> Product, '-', Date(Previous(StartDate)-1)) as nextDate
Resident Temp
Order By Product, StartDate desc;

[komminenianusha]

Thank you  very much @PrashantSangle  for your help. It is working as expected.