Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there, Can u help me to find error from my script? I need a new field for worktime called Shift.
6:00-14:00 ->morning
14:00-22:00 ->afternoon
other time -> night
------------------------------------------------
Data:
Load *,
Time(Changed, 'HH:MM:SS') as time
From [xxxxxxxxxx] (qvd);
Ranges:
LOAD * INLINE [
Start, Stop, Shift
06:00:00, 13:59:59, Morning
14:00:00, 21:59:59, Afternoon
22:00:00, 23:59:59, Night
00:00:00, 05:59:59, Night
];
IntervalMatch(time)
Left Join (Ranges)
Load Start, Stop Resident Ranges;
Drop Fields Start, Stop;
Left Join(Data)
LOAD * Resident Ranges;
--------------------------------------------------------------------------
'Changed' is a timestamp in form 'dd.mm.yyyy hh:mm:ss'. I think there are no grammtical error in this script.
But the result is so.
Shift | time |
---|---|
Morning | |
Afternoon | |
Night | |
06:26:22 | |
12:26:22 | |
12:32:25 | |
15:45:06 | |
19:02:11 | |
04:23:01 |
What I want is like so.
Shift | time |
---|---|
Morning | 06:26:22 |
Morning | 12:26:22 |
Morning | 12:32:25 |
Afternoon | 15:45:06 |
Afternoon | 19:02:11 |
Night | 04:23:01 |
I think the Problem is about 'Changed'. Because I made a test with inline table. It workes good.
---------------------------------------------------------
Test:
Load * Inline [
time
06:15:00
15:12:02
09:02:00,
21:03:26
03:03:03];
Ranges:
LOAD * INLINE [
Start, Stop, Shift
06:00:00, 13:59:59, Morning
14:00:00, 21:59:59, Afternoon
22:00:00, 23:59:59, Night
00:00:00, 05:59:59, Night
];
IntervalMatch(time)
Left Join (Ranges)
Load Start, Stop Resident Ranges;
Drop Fields Start, Stop;
left join(Test)
LOAD *
Resident Ranges;
-----------------------------------------------------------------
These two scripts are exactly the same. Only data source are different. The first one does not work, but the second works good. I think the problem is about 'Changed'.
Thank you.
You tries to match a timestamp with a time and this couldn't work. This meant you need to convert your timestamp into a time and not only formatting it, maybe with something like:
Time(frac(Changed)) as time
- Marcus
Maybe this?
Data:
Load *,
Time(timestamp#(Changed, 'dd.mm.yyyy hh:mm:ss') as time
From [xxxxxxxxxx] (qvd);
You tries to match a timestamp with a time and this couldn't work. This meant you need to convert your timestamp into a time and not only formatting it, maybe with something like:
Time(frac(Changed)) as time
- Marcus
Cool. It works. Thank you very much.
Another question.
Frac() can convert timestamp into a time. And what can convert timestamp into a Date?
Yes, it would be: date(floor(timestamp))
Hi Yihao
Try this script It will work according to your requirement.
Ranges:
LOAD * INLINE [
Start, Stop, Shift
06:00:00, 13:59:59, Morning
14:00:00, 21:59:59, Afternoon
22:00:00, 23:59:59, Night
00:00:00, 05:59:59, Night
];
Test:
Load * Inline [
time
06:15:00
15:12:02
09:02:00,
21:03:26
03:03:03];
inner join
IntervalMatch(time)
load Start,Stop
resident Ranges;