Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
tomcatxx
Creator
Creator

Ranges, Time intervall

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;

L
eft 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.

Shifttime
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.

Shifttime
Morning06:26:22
Morning12:26:22
Morning12:32:25
Afternoon15:45:06
Afternoon19:02:11
Night04: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.

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

5 Replies
stabben23
Master
Master

Maybe this?

Data:
Load *,
Time(timestamp#(Changed, 'dd.mm.yyyy hh:mm:ss') as time
From  [xxxxxxxxxx] (qvd);

marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

tomcatxx
Creator
Creator

Cool. It works. Thank you very much.

Another question.

Frac() can convert timestamp into a time. And what can convert timestamp into a Date?

marcus_sommer
MVP & Luminary
MVP & Luminary

Yes, it would be: date(floor(timestamp))

reetumishra
Partner
Partner

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;