topic Re: Ranges, Time intervall in QlikView

Ranges, Time intervall

tomcatxx — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Ranges, Time intervall

stabben23 — Thu, 05 Jul 2018 13:58:02 GMT

Maybe this?

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

Re: Ranges, Time intervall

marcus_sommer — Thu, 05 Jul 2018 14:09:11 GMT

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

Re: Ranges, Time intervall

tomcatxx — Thu, 05 Jul 2018 14:28:11 GMT

Cool. It works. Thank you very much.

Another question.

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

Re: Ranges, Time intervall

marcus_sommer — Thu, 05 Jul 2018 15:11:36 GMT

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

Re: Ranges, Time intervall

reetumishra — Mon, 09 Jul 2018 06:31:05 GMT

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;