topic Creating a column for Shift using mm/dd/yyyy/ hh/mm/ss timestamp column in App Development

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

t-gilchrist — Fri, 31 Aug 2018 14:17:11 GMT

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.

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

BalaBhaskar_Qlik — Sat, 01 Sep 2018 05:07:18 GMT

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')

))

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

rubenmarin — Sat, 01 Sep 2018 07:10:22 GMT

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',

))

)

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

t-gilchrist — Tue, 04 Sep 2018 11:48:13 GMT

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.

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

t-gilchrist — Tue, 04 Sep 2018 11:59:16 GMT

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...

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

rubenmarin — Wed, 05 Sep 2018 06:13:32 GMT

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;