topic How to calculate EndDate base on startDate in App Development

How to calculate EndDate base on startDate

komminenianusha — Mon, 14 Sep 2020 12:28:30 GMT

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.

Re: How to calculate EndDate base on startDate

PrashantSangle — Mon, 14 Sep 2020 13:02:28 GMT

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/

Re: How to calculate EndDate base on startDate

sunny_talwar — Mon, 14 Sep 2020 13:22:37 GMT

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

Re: How to calculate EndDate base on startDate

komminenianusha — Tue, 15 Sep 2020 04:49:02 GMT

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