Create End Date based on start date

[thanhphongle]

Hello community,

is it possible to create in the script an "End Date" which is based on the "start date"?

Currently i get data from SAP where the start date of a price of an article is given but the end date is not.

See the table below

Article No	Start Date	End Date	Price
1001	01.01.2020		100
1001	01.02.2020		90
1001	01.03.2020		100

Is there a possibilty to calculate the End-Date based on the Start date by calculating

if there s a previous Start Date then calculate the current data record of the start date -1 , else set End-Date = 31.12.2999

See example:

Article No	Start Date	End Date	Price
1001	01.01.2020	31.01.2020	100
1001	01.02.2020	29.02.2020	90
1001	01.03.2020	31.12.2999	100

Hope anyone can help me here.

BR Le

[Taoufiq_Zarra · Accepted solution]

Maye be :

Data:
LOAD [Article No], Date#([Start Date],'DD.MM.YYYY') as [Start Date], [End Date] , Price INLINE [
    Article No, Start Date, End Date, Price
    1001,01.01.2020,,100
    1001,01.02.2020,,90
    1001,01.03.2020,,100
];

output:
noconcatenate
load [Article No],[Start Date],if(rowno()=1,Date#('31.12.2999','DD.MM.YYYY'),Date(previous([Start Date]-1),'DD.MM.YYYY')) as [End Date] ,Price;
load * resident Data order by [Start Date] DESC;

drop table Data;

output: