negative value from date difference generated by load script

[Lucent]

Hello member,

I am trying to read text file which is included date information as below.

Nr     DATE

1       30.07.2024 10:13:25

2       30.07.2024 16:32:29

3       01.08.2024 10:11:57

4       01.08.2024 16:08:45

my goal is to calculate the date difference between each row and show the difference in front end as WasteTime.

my approach is the following.

TABLE1:

Load

Nr as Nr.

Date#("DATE", 'dd.mm.yyyy hh:mm:ss') as "DATE&TIME0",

FROM [x\xx\x.txt]

TABLE2:

NoConcatenate

Load

"DATE&TIME0" as "DATE&TIME0",

"DATE&TIME0" - Previous("DATE&TIME0") as WasteTime,
Timestamp#("DATE&TIME0" - Previous("DATE&TIME0"), 'dd') as WasteTime1,
Timestamp("DATE&TIME0" - Previous("DATE&TIME0"), 'dd') as WasteTime2,
Interval#("DATE&TIME0" - Previous("DATE&TIME0"), 'dd') as WasteTime3,
Interval("DATE&TIME0" - Previous("DATE&TIME0"), 'dd') as WasteTime4,
Date("DATE&TIME0") - Previous(Date("DATE&TIME0")) as WasteTime5

Resident TABLE1

drop table TABLE1;

##########################################################

Result in front end:

Nr     DATE&TIME0                  WasteTime   WasteTime1 WasteTime2   WasteTime3  WasteTime4   WasteTime5

1       30.07.2024 10:13:25                -                    -                 -                        -                     -                     -

2       30.07.2024 16:32:29               0.263    0.263                           30    0.263                          00                0.263

3       01.08.2024 10:11:57              -29.26    -29.26                          30    -29.26                        -29               -29.26

4       01.08.2024 16:08:45               0.247    0.247                           30    0.247                          00                0.247

WasteTime and WasteTime5 would be one of the nearest to my goal but i´d like to avoid negative value.

the date & time difference between row2 and row1 should be ca. 17.5 Hr -> 0.72 day.

this is my goal.

it would be highly appreciated if you can correct my load script, in order to get right value instead of negative.

[rubenmarin]

Hi, I would try to load Previous("DATE&TIME0") as a field, to check wich value is using every roewn to compare.

Problably you need to include an 'Order By' caluse to compare with the expected value, maybe just:

Resident TABLE1
Order by [DATE&TIME0];

[Lucent]

Hi Rubenmarin,

thank you for your prompt reply.

in order to reproduce my problem, i created one text file and load script.

in my script, i already defined "Order by" keyword. i just forgot to write it in this post.

it would be great if you try to do that again.

[Lucent]

my Load script is follwoing and text file is attached.

##############################################################

TABLE1:
LOAD
[Nr] as Nr,

Date#([DATE_TIME],'dd.mm.yyyy hh:mm:ss') as "DATE&TIME"

FROM [lib://AttachedFiles/trial.txt]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

TABLE2:
NoConcatenate
Load

"DATE&TIME",
Nr,
"DATE&TIME" - Previous("DATE&TIME") as WasteTime,
Timestamp#("DATE&TIME" - Previous("DATE&TIME"), 'dd') as WasteTime1,
Timestamp("DATE&TIME" - Previous("DATE&TIME"), 'dd') as WasteTime2,
Interval#("DATE&TIME" - Previous("DATE&TIME"), 'dd') as WasteTime3,
Interval("DATE&TIME" - Previous("DATE&TIME"), 'dd') as WasteTime4,
Date("DATE&TIME") - Previous(Date("DATE&TIME")) as WasteTime5

Resident TABLE1
order by [Nr];
drop table TABLE1;

###############################################################################

[rubenmarin · Accepted solution]

Hi, detected, this should be:

TABLE1:
LOAD
[Nr] as Nr,

Date#([DATE_TIME],'DD.MM.YYYY hh:mm:ss') as "DATE&TIME"

[Lucent]

Hi Rubenmarin,

Thank you so much! then it works properly as expected. 🙂