Dynamically assigning dates

[krmvacar]

Hi All,

if SCHADATE =10.10.2023 targetstart between 10.10.2023 08:00:00 and 11.10.2023 09:00:00

 show.  if SCHADATE =11.10.2023 targetstart between 11.10.2023 08:00:00 and 12.10.2023 09:00:00 show.

how can ı do this script dynamic?

Excel:

TARGETSTART	QUANTITY	SCHDATE
10.10.2023 08:00:00	4	10.10.2023 00:00:00
10.10.2023 08:00:00	4	10.10.2023 00:00:00
10.10.2023 10:38:00	4	10.10.2023 00:00:00
10.10.2023 12:21:00	4	10.10.2023 00:00:00
11.10.2023 08:36:00	5	10.10.2023 00:00:00
11.10.2023 08:36:00	8	10.10.2023 00:00:00
11.10.2023 13:05:00	8	10.10.2023 00:00:00
11.10.2023 13:05:00	8	10.10.2023 00:00:00
11.10.2023 17:34:00	16	10.10.2023 00:00:00
11.10.2023 22:03:00	1	10.10.2023 00:00:00
11.10.2023 23:05:00	16	10.10.2023 00:00:00
11.10.2023 23:05:00	4	10.10.2023 00:00:00
12.10.2023 01:43:00	12	10.10.2023 00:00:00
12.10.2023 01:43:00	8	10.10.2023 00:00:00
11.10.2023 08:00:00	4	11.10.2023 00:00:00
11.10.2023 08:00:00	4	11.10.2023 00:00:00
11.10.2023 10:38:00	4	11.10.2023 00:00:00
11.10.2023 12:21:00	4	11.10.2023 00:00:00
14.10.2023 01:37:00	6	11.10.2023 00:00:00
14.10.2023 01:37:00	10	11.10.2023 00:00:00
14.10.2023 06:06:00	6	11.10.2023 00:00:00
14.10.2023 06:06:00	7	11.10.2023 00:00:00
14.10.2023 06:06:00	14	11.10.2023 00:00:00

I want to result:

SCHDATE	QUANTITY
10.10.2023 00:00:00	29
11.10.2023 00:00:00	16

[Happy_Mask_Salesman · Accepted solution]

What about this?

What I am doing may not be the best solution, I believe there are more ways to achieve this.

Facts:
LOAD *,
if( Timestamp(SCHDATE+1+0.375) < TARGETSTART,
0, 1) as GROUP;
LOAD Timestamp#(TARGETSTART, 'DD.MM.YYYY hh:mm:ss') as TARGETSTART,
QUANTITY,
Timestamp#(SCHDATE, 'DD.MM.YYYY hh:mm:ss') as SCHDATE
FROM
[path]

What I am doing is grabbing the SCHDATE, adding a full day (11.10.2023 00:00:00), and then add 9 hours with 0.375

After that, I ask if the TARGETSTART is below that value (11.10.2023 09:00:00), store either a zero or one depending on the result.

Then in the following chart, I have this expression:

Sum({<GROUP={1}>}QUANTITY)

Is this what you seeked for?

Regards,