How to calculate the difference in days between two dates in the same column

[Checco]

In input of the script I have a table like:

How can I calculate, for each row, the difference between the date and the date of the next row?
I would like to get a table like the one below

thank All

[SchalkF]

Hi Checco,

Do you want to achieve this in the load editor or in a table visualization?

In your load editor you would use  the following statement to get the value in the next row:

Day#(DATA - Peek(DATA, RowNo()+1), 'dd')

Kind regards

[Checco]

Thanks Schalkf

Excuse me but if I submit the script

Tab1:
LOAD ID,
DATA
FROM [File1.xlsx] (ooxml, embedded labels, table is Foglio1);

RESULT:
LOAD ID, DATA,
(DATA - Peek(DATA, RowNo() +1 ), 'dd') as day
RESIDENT Tab1
ORDER BY ID;

I get the error:

[marcus_sommer]

Try it with:

Tab1:
LOAD ID,
DATA
FROM [File1.xlsx] (ooxml, embedded labels, table is Foglio1);

RESULT:
LOAD ID, DATA,
DATA - previous(DATA) as DataDiff
RESIDENT Tab1
ORDER BY ID, DATA desc;

- Marcus

[Checco]

Hello everybody.
I tried to modify my load script (see attached file).
Can anyone tell me why the "Final Table" gives me the error in the last row: the date after 13/07/2022 is 12/07/2022 and not 18/07/2022 !!!!

Thank you all

[marcus_sommer · Accepted solution]

Just try it with the suggested approach. Further your peek() solution didn't fetch the data from the current load else it looped through the previous table - that's very seldom a sensible method.

To see what really happens you may add a recno() and a rowno() to comprehend which origin records where now loaded in which order - that's the essential part to fetch the right data from the wanted rows.

- Marcus

[Checco]

thank 1000 Marcus