Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
t-gilchrist
Contributor III
Contributor III

Creating a column for Shift using mm/dd/yyyy/ hh/mm/ss timestamp column

Hello all,

I'm looking to create a column for "Shift" (A,B,C,D,E) using a mm/dd/yyyy/ hh/mm/ss timestamp column inside my data table under data manager.

I'm extremely new to Qlik but have experience using excel/vba. Through google searches I've made it as far as...

data manager>edit this table>add field>calculate field>

I've started with just trying to get an expression that separates to...

A shift - 10:30pm-6:30am

B Shift - 6:30am-2:30pm

C Shift - 2:30pm-10:30pm


Eventually my end goal would be to include D and E shift

A shift - 10:30pm-6:30am Mon-Fri

B Shift - 6:30am-2:30pm Mon-Fri

C Shift - 2:30pm-10:30pm Mon-Fri

D Shift - 10:30pm-10:30am Sat-Sun

E Shift - 10:30am-10:30pm Sat-Sun

My current expression is...

if(Time([Time Quarantined]) >= MakeTime(10,30,00)

and Time([Time Quarantined]) < MakeTime(06,30,00),'A')

if(Time([Time Quarantined]) >= MakeTime(06,30,00)

and Time([Time Quarantined]) < MakeTime(02,30,00),'B')

if(Time([Time Quarantined]) >= MakeTime(02,30,00)

and Time([Time Quarantined]) < MakeTime(10,30,00),'C')

I get a "Missing Right parenthesis" error when I do this.

If I do..

if(Time([Time Quarantined]) >= MakeTime(10,30,00) ,'A')

Then It will create the column but the entire columns result is "A" so obviously I have more than just syntax errors in my expression.

I'm looking for help of any kind. Some insight on why my first expressions gets a parenthesis error, why the second expression shows "a" as a results for all rows in the column, examples of how I should do it, if including D and E is possible in a Qlik expression, locations of resources to better understand Qlik, and if there is any sort of debugger or step through function in Qlik where I can troubleshoot longer expressions similar to the "evaluation formula" function in excel.

Thanks for any help in advance.

5 Replies
balabhaskarqlik

When you are using more if conditions you're not supposed to close the brackets as you mentioned.

When you closed the first if clause after the value A, it won't consider the remaining condition into if clause.

So use the below expression:

=if(Time([Time Quarantined]) >= MakeTime(10,30,00) and Time([Time Quarantined]) < MakeTime(06,30,00),'A',

  if(Time([Time Quarantined]) >= MakeTime(06,30,00) and Time([Time Quarantined]) < MakeTime(02,30,00),'B',

  if(Time([Time Quarantined]) >= MakeTime(02,30,00) and Time([Time Quarantined]) < MakeTime(10,30,00),'C')

))

rubenmarin

Hi, a few thing to comment:

- 0:00 pm is 22 hours, not 10

- There is no time that can be higher than 10:30 and lower than 6:30

- To add D and E you can add a check using weekday()

So, maybe:

If (WeekDay([Time Quarantined])<5,

  if(Time([Time Quarantined]) >= MakeTime(22,30,00) or Time([Time Quarantined]) < MakeTime(06,30,00),'A',

  if(Time([Time Quarantined]) >= MakeTime(06,30,00) and Time([Time Quarantined]) < MakeTime(14,30,00),'B',

  if(Time([Time Quarantined]) >= MakeTime(14,30,00) and Time([Time Quarantined]) < MakeTime(22,30,00),'C'

  ))),

  if(Time([Time Quarantined]) >= MakeTime(22,30,00) or Time([Time Quarantined]) < MakeTime(10,30,00),'D',

  if(Time([Time Quarantined]) >= MakeTime(10,30,00) and Time([Time Quarantined]) < MakeTime(22,30,00),'E',

  ))

)

t-gilchrist
Contributor III
Contributor III
Author

Thank you for taking the time to reply.

Using the provided expression I get a "Missing left term" error.

Removing the "=" at the beginning of the expression I'm left with a "Missing right parenthesis" error.

Using only the first shifts conditions...

I am still getting the parenthesis error.

t-gilchrist
Contributor III
Contributor III
Author

Thank you for taking the time to reply.

Hi, a few thing to comment:

- 0:00 pm is 22 hours, not 10. It makes sense why the test with acceptable syntax did not behave as expected now.

- There is no time that can be higher than 10:30 and lower than 6:30. Opps, yes that was meant to be an or.

- To add D and E you can add a check using weekday(). Perfect, I was hoping that function would work.

Using the full expression you provided I am getting the "Missing right parenthesis" error.

Using only the first shift of the expression and excluding day-of-week, I am getting the "Missing right parenthesis" error...

rubenmarin

Hi Trey, I see it correct, maybe it's a bug in the syntax checker or some limitation when creating calculated fields in data manager.

You can try adding it in script editor, keep the synched script section as it is (creating a backup of your app before editing the script can be a good idea), and add a new script section after the synched section,, the script can be:

Rename oldTableName to newTableName;

oldTableName:

Noconcatenate

LOAD *,

    If(Time([Time Quarantined....

Resident newTableName;

DROP Table newTableName;