topic Re: Re: Re: Re: Time in QlikView

Time

realpixel — Sat, 30 Nov 2013 12:02:33 GMT

Hello,

I have this expression

Time(time#([HMHEURCR],'hhmmss'),'hh:mm:ss') as HEURE,

It's possible to create a new field all hours between 07:00:00 and 19:00:00 have the value "SHIFT1" and all hours between 19:00:00 and 07:00:00 have the value "SHIFT2"?

Someting like that

HEURE	SHIFT
06:13:01	SHIFT1
09:33:06	SHIFT1
10:52:20	SHIFT1
11:53:07	SHIFT1
12:52:40	SHIFT1
13:59:42	SHIFT1
14:59:13	SHIFT1
15:56:41	SHIFT1
16:58:53	SHIFT1
17:57:08	SHIFT1
18:55:07	SHIFT1
19:01:03	SHIFT2
20:54:07	SHIFT2
21:50:08	SHIFT2
22:49:44	SHIFT2
23:57:38	SHIFT2

Re: Time

Anonymous — Sat, 30 Nov 2013 12:49:37 GMT

In your load script you could create a flag field with something like:

if ( Hour(time#([HMHEURCR],'hhmmss'),'hh:mm:ss') >= 7 ,

if ( Hour(time#([HMHEURCR],'hhmmss'),'hh:mm:ss') <= 19 ,

if ( SHIFT = 'SHIFT2' ,

1 , 0 ) ) ) as DayShift2Flag ;

Best Regards, Bill

Re: Re: Time

realpixel — Sat, 30 Nov 2013 14:52:47 GMT

I have the following error when I reload.

Error in expression:

Hour takes 1 parameter

I try to put at the end of my script without success, in attachement original script

Re: Re: Re: Time

Anonymous — Sat, 30 Nov 2013 15:43:25 GMT

How about this, you can test via the Inline Load:

Load

* ,

if ( Hour ( time# ( [HEURE],'hh:mm:ss' ) ) >= 7 ,

if ( Hour ( time# ( [HEURE],'hh:mm:ss' ) ) <= 19 ,

'SHIFT1' , 'SHIFT2' ) , 'SHIFT2' )

as SHIFT ;

LOAD * INLINE [

HEURE

06:13:01

09:33:06

10:52:20

11:53:07

12:52:40

13:59:40

14:59:13

15:56:41

16:58:53

17:57:08

18:55:07

19:01:03

20:54:07

21:50:08

22:49:44

23:57:38

];

Best Regards, Bill

Re: Re: Re: Time

realpixel — Mon, 02 Dec 2013 09:45:13 GMT

Thank you for your reply.

It's works fine with inline command, the hours is not always the same, it's change every day.

I cannot refill every days the hours.

There is an another solution to solve my problem?

Re: Time

Anonymous — Mon, 02 Dec 2013 09:50:33 GMT

The Inline Load was purely for me to test it, having not tested it the first time & got it wrong.

You should be able to copy the relevant lines into your load script against your real daily data load, and if needs be adjust to for your real world scenario.

Any further questions, then please ask.

Best Regards, Bill

Re: Re: Re: Re: Time

tresB — Mon, 02 Dec 2013 10:25:37 GMT

If Bill Markham 's solution works for you in inline, it should work in your script as well. Place Bill's code below the load code section of your code like attached script.

Re: Re: Re: Re: Time

realpixel — Mon, 02 Dec 2013 10:42:11 GMT

I add the following line in my scipt.

The first line already exist n my script

Time(time#([HMHEURCR],'hhmmss'),'hh:mm:ss') as HEURE,
if ( Hour ( time# ( [HEURE],'hh:mm:ss' ) ) >= 7 ,
if ( Hour ( time# ( [HEURE],'hh:mm:ss' ) ) <= 19 ,
'SHIFT1' , 'SHIFT2' ) , 'SHIFT2' ) as SHIFT,

I try also to add at the end of script

if(Hour(time#([HEURE],'hh:mm:ss'))>=7,if(Hour(time#([HEURE],'hh:mm:ss' ) ) <= 19 ,'SHIFT1','SHIFT2'),'SHIFT2') as SHIFT;

I have the following error

Field not found - <HEURE>

SELECT*

FROM DWHCG.dbo.oscar_livraison where (HMMOUV='ENVC' or HMMOUV='LIPN' or HMMOUV='DEPO' or HMMOUV='SOVC')

Re: Re: Re: Re: Time

tresB — Mon, 02 Dec 2013 10:45:23 GMT

Replace HEURE by HMHEURCR, like:

if(Hour(time#([HMHEURCR],'hh:mm:ss'))>=7,if(Hour(time#([HMHEURCR],'hh:mm:ss' ) ) <= 19 ,'SHIFT1','SHIFT2'),'SHIFT2') as SHIFT

Re: Re: Re: Re: Time

realpixel — Mon, 02 Dec 2013 13:07:28 GMT

I modified HEURE by HMHEURCR now all value in shift field appears with value "SHIFT2"