Frac() function

[#1]

Hi Experts,

i m trying to create a field with the following If condition.

IF(FRAC(TIMESTAMP) >'06:30:00' and FRAC(TIMESTAMP)<='18:30:00','06:30 - 18:30',     

IF(FRAC(TIMESTAMP) >'18:30:00' and FRAC(TIMESTAMP)<='06:30:00','18:30 - 06:30',
IF(FRAC(TIMESTAMP) >'06:45:00' and FRAC(TIMESTAMP)<='18:45:00','06:45 - 18:45',
IF(FRAC(TIMESTAMP) >'18:45:00' and FRAC(TIMESTAMP)<='06:45:00','18:45 - 06:45')))) AS  SHIFTHOURS

I m expecting to get the field values as:

SHIFTHOURS:

06:30 - 18:30

18:30 - 06:30

06:45 - 18:45

18:45 - 06:45

But my actual results are

06:30 - 18:30

06:45 - 18:45

I m missing two more values in the field. Am i doing anything wrong?

can someone please help me to correct the expression?

[Sergey_Shuklin]

Hello!

IF function is moving step by step, so if the value is 6:50 it will stop at first condition >'06:30:00', don't reach >'06:45:00', and will continue to check rows. It's not actually answers your question, but you can correct your conditions as a start.

[prat1507]

Hi

Maybe this

IF(FRAC(TIMESTAMP) >'06:30:00' and FRAC(TIMESTAMP)<='18:30:00','06:30 - 18:30',    

IF(FRAC(TIMESTAMP) >'18:30:00' or FRAC(TIMESTAMP)<='06:30:00','18:30 - 06:30',
IF(FRAC(TIMESTAMP) >'06:45:00' and FRAC(TIMESTAMP)<='18:45:00','06:45 - 18:45',
IF(FRAC(TIMESTAMP) >'18:45:00' or FRAC(TIMESTAMP)<='06:45:00','18:45 - 06:45')))) AS  SHIFTHOURS

Regards

Pratyush

[Anil_Babu_Samineni]

I assume, In your second condition for 24 Hours need to change it to 12 Hours. Can you try this, May be?

IF(FRAC(TIMESTAMP) >'06:30:00' and FRAC(TIMESTAMP)<='18:30:00','06:30 - 18:30',    

IF(FRAC(Time(Time#(TIMESTAMP,'YourFieldFormat'),'hh:mm:ss')) >'18:30:00' and FRAC(TIMESTAMP)<='06:30:00','18:30 - 06:30',
IF(FRAC(TIMESTAMP) >'06:45:00' and FRAC(TIMESTAMP)<='18:45:00','06:45 - 18:45',
IF(FRAC(Time(Time#(TIMESTAMP,'YourFieldFormat'),'hh:mm:ss')) >'18:45:00' and FRAC(TIMESTAMP)<='06:45:00','18:45 - 06:45')))) AS  SHIFTHOURS

[PrashantSangle]

Hi,

Not understood your requirement properly.

Can u explain your requirement with sample data and required output.

Regards,

Prashant

[#6]

Pratyush,

this is not working

[#7]

Hi Anil,

can you please help me with the correct expression?

[marcus_malinow]

Your time intervals overlap.

What result do you expect to see if TIMESTAMP is 07:00?

- it could fall into either the '06:30 - 18:30' or '06:45 - 18:45' shift

[#9]

Hi Marcus,

if the TIMESTAMP is 07:00, then it should fall on both '06:30 - 18:30' & '06:45 - 18:45' shifts.

is it possible?

[marcus_malinow]

Yes, it's possible, however the solution is slightly different:

IntervalMatch(TIMESTAMP)

LOAD * INLINE [

Start, End

06:30, 18:30

00:00, 06:30

18:30, 24:00

06:45, 18:45

00:00, 06:45

18:45, 24:00];

then left join on your descriptive field.