6 Replies Latest reply: Nov 4, 2016 7:42 AM by Ian Crosland RSS

    Qlik Sense - Data Load - Add Calculated Field

    Giles Walker

      New Qlik Sense User here (1.5 weeks in!).  Can anyone steer me right on this please?  Doing a basic data load from Excel file to learn basic "how to" within Sense data manager.  Data has been brought in from Excel and I am editing the table before I load to Qlik Sense.  When I click on the edit button, the data table is visible, and I can click on the add field button, to insert a calculated field.  I have managed to get some basic functions working here, such as basic concatenation, and I also managed to extract the time portion of a DateTime field using what I found on a post in the community (Date and time from TimeStamp).

       

      However, I cannot make any IF statements work in the desired manner.  All I want to do is use the extracted time field and calculate the following........... if the time is less than 1pm (1:00:00 PM) then the value returned should equal the date portion of the DateTime field, BUT if the time is after 1pm, then the value returned should equal the date portion of the DateTime field + 1 day.

       

      The basic premise of the report I am building is that our warehouse has order cut off at 1pm, and therefore to calculate delivery KPIs I need to apportion orders into a bucket to then have the correct delivery KPI calculate.  Orders before 1pm fall into today's bucket, and orders after 1pm drop into tomorrow's bucket.

       

      My main issue is that the expression builder just keeps rejecting things like the = sign, saying "unrecognized symbol".

       

      I have been searching blogs and web pages for help.  I am sure I am just approaching this wrong, but I thought doing the work in the data load area was the right thing to do?  It would seem wrong to do this leg work anywhere else.

       

      If anyone can help me do this through the generic user interface I would really appreciate it.  I haven't got to grips with the data load editor yet.  Cheers from the end of the earth, ie New Zealand!