Problems categorising timestamps

[davehutchinson]

Hi, I've been stuck on this all day and its driving me nuts.  I've gone through similar posts but haven't been able to crack it.  Even help from one of my experienced colleagues didn't crack it...

I have calculated the time duration between 2 time stamps.  I now want to categorise this time duration.

The first part of the script works fine:

[vw_ngd_consumer_ee_pn_trdb.test_initiate_time] as "RunTime_Initiate_Time",
[vw_ngd_consumer_ee_pn_trdb.test_completion_time] as "RunTime_Completion_Time",

Time(Timestamp([vw_ngd_consumer_ee_pn_trdb.test_completion_time],'YYYY-MM-DD hh:mm:ss')-timestamp([vw_ngd_consumer_ee_pn_trdb.test_initiate_time],'YYYY-MM-DD hh:mm:ss'),'hh:mm:ss') as "NGD Run Time",

Now here's where my issue is. I want to create a new table that categorises this field.  Alternatively I could add this categorisation as an extra column in the original dataset, but I thought it might be easier to create a new table.  Maybe now?

So the script I've created currently looks like this:

Run_Time_Table:
LOAD
[NGD Run Time],
IF(Time([NGD Run Time]),'hh:mm:ss')<=('00:01:00'),'Less than 1 minute',
IF(Time([NGD Run Time]),'hh:mm:ss')<=Time('00:02:00'),'Less than 2 minutes',
IF(Time([NGD Run Time]),'hh:mm:ss')<=Time('00:03:00'),'Less than 3 minutes',
IF(Time([NGD Run Time]),'hh:mm:ss')<=Time('00:03:00'),'greater than 3 minutes','Unknown' as "RunTime_Category"


Resident [NGD_Test_Data];

This results in the following error message:

I'm completely stumped as to what the issue is.  I've checked the field names and I can't see any duplicate fields, but obviously I am using [NGD Run Time] field a few times, but that should be fine?

Can anyone spot where I'm going wrong?

[marksouzacosta]

You are missing closing parenthesis in your if statement.

[BrunPierre · Accepted solution]

Hi, a minor syntax issue with the placement of the parentheses.

IF(Time([NGD Run Time]),'hh:mm:ss')<=('00:01:00'),'Less than 1 minute',
IF(Time([NGD Run Time]),'hh:mm:ss')<=Time('00:02:00'),'Less than 2 minutes',
IF(Time([NGD Run Time]),'hh:mm:ss')<=Time('00:03:00'),'Less than 3 minutes',
IF(Time([NGD Run Time]),'hh:mm:ss')<=Time('00:03:00'),'greater than 3 minutes','Unknown' )))) as "RunTime_Category"

[davehutchinson]

Ahh thanks! So that has categorised the run times now, but not correctly... 😞

Here's my script: 

But the data is categorised like this: 

As you can see they're wrong....  Do you know why this might be? Will keep searching!

[davehutchinson]

Sorted!!! Just needed a small amendment to my IF statement:

IF(Time([NGD Run Time],'hh:mm:ss')<('00:01:00'),'Less than 1 minute',
IF(Time([NGD Run Time],'hh:mm:ss')<Time('00:02:00'),'Less than 2 minutes',
IF(Time([NGD Run Time],'hh:mm:ss')<Time('00:03:00'),'Less than 3 minutes',
IF(Time([NGD Run Time],'hh:mm:ss')>=Time('00:03:00'),'greater than 3 minutes','Unknown')))) as "RunTime_Category",

Thanks for your help all!!!