Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Experts,
I need help to solve one problem.
Example data : The Day starts at 5:00 AM and ends at next day 3:00 AM (Working Hours)
Date | Hour | Sales Amount | Total Sales(Running Total) |
11/12/2022 | 5 | 60 | 60 |
11/12/2022 | 6 | 40 | 100 |
11/12/2022 | 7 | 200 | 300 |
11/12/2022 | 8 | 300 | 600 |
11/12/2022 | 9 | 1000 | 1600 |
11/12/2022 | 10 | 1600 | 3200 |
11/12/2022 | 11 | 500 | 3700 |
11/12/2022 | 12 | 2000 | 5700 |
11/12/2022 | 13 | 1100 | 6800 |
11/12/2022 | 14 | 60 | 6860 |
11/12/2022 | 15 | 40 | 6900 |
11/12/2022 | 16 | 200 | 7100 |
11/12/2022 | 17 | 300 | 7400 |
11/12/2022 | 18 | 1000 | 8400 |
11/12/2022 | 19 | 1600 | 10000 |
11/12/2022 | 20 | 500 | 10500 |
11/12/2022 | 21 | 2000 | 12500 |
11/12/2022 | 22 | 1100 | 13600 |
11/12/2022 | 23 | 1600 | 15200 |
12/12/2022 | 0 | 500 | 15700 |
12/12/2022 | 1 | 2000 | 17700 |
12/12/2022 | 2 | 1100 | 18800 |
12/12/2022 | 3 | 20 | 18820 |
Now I want to refresh this data every hour based on the current time. For example if the current time is 12:00 PM it should only show the data from 5 to 12 hrs, if current time is 18:00 it should show data from 5 to 18 hrs and if the current time is 02 AM it should show show data from 5 to 2 hrs.
I hope i am clear with the question 🙂
Kindly help
Ok, so basically, as I understand, logic should be so: if reload Hour is <5, you load everything from yesterday and some part of today. If hour is >=5 you take everything from today till that Hour. This can be implemented by 'If..then..elseif..else..end if' statement:
sample:
load * inline [
Date , Hour
2022-12-12 , 5
2022-12-12 , 6
2022-12-12 , 7
2022-12-12 , 8
2022-12-12 , 9
2022-12-12 , 10
2022-12-12 , 11
2022-12-12 , 12
2022-12-12 , 13
2022-12-12 , 14
2022-12-12 , 15
2022-12-12 , 16
2022-12-12 , 17
2022-12-12 , 18
2022-12-12 , 19
2022-12-12 , 20
2022-12-12 , 21
2022-12-12 , 22
2022-12-12 , 23
2022-12-13 , 0
2022-12-13 , 1
2022-12-13 , 2
2022-12-13 , 3];
// LET vHour = 1; //for testing
LET vHour = hour(ReloadTime());
IF $(vHour)<5 /*or just <=3*/ THEN
NoConcatenate
main:
LOAD *
resident sample
WHERE Date=today()-1 OR (Hour<=$(vHour) and Date=today());
ELSE
NoConcatenate
main:
LOAD *
resident sample
WHERE Hour<=$(vHour) and Date=today();
END IF
drop table sample;
Hi, in load script add WHERE clause to filter your data:
LOAD *
FROM your_source
WHERE Hour<=hour(ReloadTime()) and Date=today();
@justISO Thank you very much for the reply, I tried the solution i am getting the data less than the ReloadTime but the result is not exactly the same as required because in this way i am getting all the hours less than the current reload hour
example: I run the data at 9:00 am and its fetching the data of 0,1,2,3,5,6,7,8,9 but it should only fetch the data from the start of the day 5,6,7,8,9 as shown in the table.
Please help in this 🙂
Ok, so basically, as I understand, logic should be so: if reload Hour is <5, you load everything from yesterday and some part of today. If hour is >=5 you take everything from today till that Hour. This can be implemented by 'If..then..elseif..else..end if' statement:
sample:
load * inline [
Date , Hour
2022-12-12 , 5
2022-12-12 , 6
2022-12-12 , 7
2022-12-12 , 8
2022-12-12 , 9
2022-12-12 , 10
2022-12-12 , 11
2022-12-12 , 12
2022-12-12 , 13
2022-12-12 , 14
2022-12-12 , 15
2022-12-12 , 16
2022-12-12 , 17
2022-12-12 , 18
2022-12-12 , 19
2022-12-12 , 20
2022-12-12 , 21
2022-12-12 , 22
2022-12-12 , 23
2022-12-13 , 0
2022-12-13 , 1
2022-12-13 , 2
2022-12-13 , 3];
// LET vHour = 1; //for testing
LET vHour = hour(ReloadTime());
IF $(vHour)<5 /*or just <=3*/ THEN
NoConcatenate
main:
LOAD *
resident sample
WHERE Date=today()-1 OR (Hour<=$(vHour) and Date=today());
ELSE
NoConcatenate
main:
LOAD *
resident sample
WHERE Hour<=$(vHour) and Date=today();
END IF
drop table sample;
@justISO seems like this is not what OP wanted, as your first where condition will include all records of both yesterday and today until current hour.
Maybe something like:
load * from XXX
where date(floor(timestamp(date#(Date,'DD/MM/YYYY') + time#(Hour,'hh') - '05:00:00'))) = today() and Hour <= hour(now())
HI @justISO this code is working find for me, now i want to update something in the code can you help me with that?
Hi @justISO,
seeking your suggestion to achieve the required output. Now i have two additional fields in the data:
Date | Hour | Half Hour | Half Hour 2 | Sales Amount |
11/12/2022 | 5 | 0 | 05:00-05:29 | 60 |
11/12/2022 | 5 | 1 | 05:30-05:59 | 23 |
11/12/2022 | 6 | 0 | 06:00-06:29 | 40 |
11/12/2022 | 6 | 1 | 06:30-06:59 | 45 |
11/12/2022 | 7 | 0 | 07:00-07:29 | 200 |
11/12/2022 | 7 | 1 | 07:30-07:59 | 422 |
11/12/2022 | 8 | 0 | 08:00-08:29 | 300 |
11/12/2022 | 8 | 1 | 08:30-08:59 | 456 |
11/12/2022 | 9 | 0 | 09:00-09:29 | 1000 |
11/12/2022 | 9 | 1 | 09:30-09:59 | 356 |
11/12/2022 | 10 | 0 | 10:00-10:29 | 1600 |
11/12/2022 | 10 | 1 | 10:30-10:59 | 432 |
11/12/2022 | 11 | 0 | 11:00-11:29 | 500 |
11/12/2022 | 11 | 1 | 11:30-11:59 | 754 |
11/12/2022 | 12 | 0 | 12:00-12:29 | 2000 |
11/12/2022 | 12 | 1 | 12:30-12:59 | 634 |
11/12/2022 | 13 | 0 | 13:00-13:29 | 1100 |
11/12/2022 | 13 | 1 | 13:30-13:59 | 456 |
11/12/2022 | 14 | 0 | 14:00-14:29 | 60 |
11/12/2022 | 14 | 1 | 14:30-14:59 | 346 |
11/12/2022 | 15 | 0 | 15:00-15:29 | 40 |
11/12/2022 | 15 | 1 | 15:30-15:59 | 234 |
11/12/2022 | 16 | 0 | 16:00-16:29 | 200 |
11/12/2022 | 16 | 1 | 16:30-16:59 | 232 |
11/12/2022 | 17 | 0 | 17:00-17:29 | 300 |
11/12/2022 | 17 | 1 | 17:30-17:59 | 123 |
11/12/2022 | 18 | 0 | 18:00-18:29 | 1000 |
11/12/2022 | 18 | 1 | 18:30-18:59 | 221 |
11/12/2022 | 19 | 0 | 19:00-19:29 | 1600 |
11/12/2022 | 19 | 1 | 19:30-19:59 | 567 |
11/12/2022 | 20 | 0 | 20:00-20:29 | 500 |
11/12/2022 | 20 | 1 | 20:30-20:59 | 255 |
11/12/2022 | 21 | 0 | 21:00-21:29 | 2000 |
11/12/2022 | 21 | 1 | 21:30-21:59 | 2234 |
11/12/2022 | 22 | 0 | 22:00-22:29 | 1100 |
11/12/2022 | 22 | 1 | 22:30-22:59 | 235 |
11/12/2022 | 23 | 0 | 23:00-23:29 | 1600 |
11/12/2022 | 23 | 1 | 23:30-23:59 | 1766 |
12/12/2022 | 0 | 0 | 00:00-00:29 | 500 |
12/12/2022 | 0 | 1 | 00:30-00:59 | 35 |
12/12/2022 | 1 | 0 | 01:00-01:29 | 634 |
12/12/2022 | 1 | 1 | 01:30-01:59 | 335 |
12/12/2022 | 2 | 0 | 02:00-02:29 | 1100 |
12/12/2022 | 2 | 1 | 02:30-02:59 | 532 |
12/12/2022 | 3 | 0 | 03:00-03:29 | 20 |
12/12/2022 | 3 | 1 | 03:30-03:59 | 234 |
Output should be like for example:
If I run the data at 9:30 am it should fetch the data from the start of the day 05 to 9:30 (from 05:00-05:29 to 09:00-09-30).
It would be really helpful if you can suggest some idea.