Time

[realpixel]

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

[Anonymous]

Hi

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

[realpixel]

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

[Anonymous]

Hi

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

[realpixel]

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?

[Anonymous]

Hi

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

[tresesco]

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.

[realpixel]

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')

[tresesco]

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

[realpixel]

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