topic Re: Frac() function in QlikView

Frac() function

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Frac() function

Sergey_Shuklin — Wed, 21 Jun 2017 12:14:24 GMT

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.

Re: Frac() function

prat1507 — Wed, 21 Jun 2017 12:15:16 GMT

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

Re: Frac() function

Anil_Babu_Samineni — Wed, 21 Jun 2017 12:19:53 GMT

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

Re: Frac() function

PrashantSangle — Wed, 21 Jun 2017 12:20:00 GMT

Hi,

Not understood your requirement properly.

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

Regards,

Prashant

Re: Frac() function

Wed, 21 Jun 2017 12:23:51 GMT

Pratyush,

this is not working

Re: Frac() function

Wed, 21 Jun 2017 12:24:18 GMT

Hi Anil,

can you please help me with the correct expression?

Re: Frac() function

marcus_malinow — Wed, 21 Jun 2017 12:30:00 GMT

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

Re: Frac() function

Wed, 21 Jun 2017 12:44:49 GMT

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?

Re: Frac() function

marcus_malinow — Wed, 21 Jun 2017 12:52:14 GMT

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.

Re: Frac() function

PrashantSangle — Wed, 21 Jun 2017 13:00:28 GMT

Hi,

You want output like this manner then if else wont help you

Try with Switch case when.

Syntax :

switch expression { case valuelist [ statements ]} [ default statements ]end switch

Search in help menu for details.

Regards,

Prashant