topic If condition needs a modification in QlikView

If condition needs a modification

Wed, 14 Dec 2011 15:18:22 GMT

Hi,

I have a table where start time and end time with date available.

St Dt	st Time	end dt	end time
11/11/11	8:20:12 AM	11/11/11	10:00:20 PM
11/11/11	6:20:12 AM	11/12/11	8:00:20 PM
11/11/11	10:20:12 AM	11/15/11	6:00:20 PM
11/11/11	2:20:12 PM	11/11/11	5:00:20 PM

In the chart,I am trying to do some calculatation on those time range which belong to 7AM to 7PM.Expected result would be

St Dt	St time	end dt	end time	calc
11/11/11	8:20:12 AM	11/11/11	10:00:20 PM	exceed Range 7 AM to 7 PM
11/11/11	6:20:12 AM	11/12/11	8:00:20 PM	exceed Range 7 AM to 7 PM
11/11/11	10:20:12 AM	11/15/11	6:00:20 PM	calculated value
11/11/11	2:20:12 PM	11/11/11	5:00:20 PM	calculated value

I have two variables

Set vStart=maketime(7);

Set vEnd=maketime(19);

For the Calc I am using

if (Start_Time < $(vStart) or End_time >$(vEnd),'exceed Range 7 AM to 7 PM',Time(Tot_Hrs-NonWork_Hrs) ) as calc;

the above if condition is not working.Can someone help on this?

Thanks,Pooja

If condition needs a modification

brenner_martina — Thu, 15 Dec 2011 15:36:19 GMT

Hi, Pooja,

your script must look like this:

let vStart=Num(maketime(7));
let vEnd=Num(maketime(19));

LOAD
Start_Date,
Start_Time,
End_Date,
End_Time,
if (Num(Start_Time) < $(vStart) or Num(End_Time) >$(vEnd),'exceed Range 7 AM to 7 PM',Time(End_Time-Start_Time) ) as calc

INLINE [
    Start_Date, Start_Time, End_Date, End_Time
    11.11.2011, 8:20:12 AM, 11.11.2011, 10:00:20 PM
    11.11.2011, 6:20:12 AM, 11.12.2011, 8:00:20 PM
    11.11.2011, 10:20:12 AM, 15.11.2011, 6:00:20 PM
    11.11.2011, 2:20:12 PM, 11.11.2011, 5:00:20 PM
];

Greetings from Germany

Martina

EVACO GmbH

If condition needs a modification

Thu, 15 Dec 2011 16:26:35 GMT

Thanks a lot!