Update "Date until" with new "Date from" -1

[wunderch]

Hi guys,

I have a straight table with RecordNo, product, Date from and Date until.

RecordNo	product	Date from	Date until
1	A	08.06.2021	31.12.9999
2	B	08.06.2021	31.12.9999
3	C	08.06.2021	31.12.9999
4	A	15.06.2021	31.12.9999
5	B	15.06.2021	31.12.9999
6	A	18.06.2021	31.12.9999

Now I want to do:

If a product have a new entry with a newer "Date from", I want to update the "Date until" in the row before with the "Date from" -1 from the newer row. So that I get the following table:

RecordNo	product	Date from	Date until
1	A	08.06.2021	14.06.2021
2	B	08.06.2021	14.06.2021
3	C	08.06.2021	31.12.9999
4	A	15.06.2021	17.06.2021
5	B	15.06.2021	31.12.9999
6	A	18.06.2021	31.12.9999

I hope, you understand what I mean.

Any ideas?

Kindly regards

Chris

[Taoufiq_Zarra]

@wunderch  in load script ?

[wunderch]

Yes, I want to do this in load scrpt.

[pabloviera]

Hello Wunderch,

one approach could be doing a resident load of your data, sorting it by product and "Date from" descending (And no concatente of course)

Then use the previous function to change de "Date Until" in the next entry of the same product

Something like this (You can ignore the date interpretation function,I had to use it because my default dates are different)

NoConcatenate
EntriesOrdered:
Load
RecordNo as RN,
product as prod,
"Date from" as DateF,
if(Previous(product)=product,date(date#(previous("Date from"),'DD.MM.YYYY')-1,'DD.MM.YYYY'), "Date until") as DateU
Resident Entries order by product, "Date from" desc;

Regards

[Taoufiq_Zarra · Accepted solution]

@wunderch  you can use :

Tmp:

LOAD * INLINE [
    RecordNo, product, Date from, Date until
    1, A, 08.06.2021, 31.12.9999
    2, B, 08.06.2021, 31.12.9999
    3, C, 08.06.2021, 31.12.9999
    4, A, 15.06.2021, 31.12.9999
    5, B, 15.06.2021, 31.12.9999
    6, A, 18.06.2021, 31.12.9999
];
left join 

load product, count([Date from]) as CTmp resident Tmp group by product;

output:
noconcatenate

load RecordNo, product, [Date from],if(product=peek(product) and CTmp>1,Date(peek([Date from])-1),[Date until]) as [Date until];
load * resident Tmp order by product,[Date from] DESC;

drop table Tmp;

output:

[wunderch]

Yes, it works!!!!! That's exactly what I want!!!!

Many thanks for your reply, you made my day!!!

Regards 

Chris