Qlik Sense - Data Load - Add Calculated Field

[gileswalker]

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!

[brunobertels]

Hi Giles

Try to add in your script this :

if(

timestamp([YourDateField],'h:mm:ss TT')>='1:00:00 PM',

    Date(Floor(Date([YourDateField],'DD MMMM YYYY hh:mm')+1),'DD/MM/YYYY'),

    Date([YourDateField],'DD/MMM/YYYY')

) as NewDate,

This one may work also :

if(

hour(

timestamp([YourDateField],'h:mm:ss TT'))>='1:00:00 PM',

floor(date([YourDateField],'DD/MMM/YYYY'))+1,

Date([YourDateField],'DD/MMM/YYYY')) as NewDate,

[gileswalker]

Hi Bruno - thanks for taking the time to reply to me .   I really appreciate it.

Your script is obviously good, but in the editor I am using (as part of data manager, not data load editor), Qlik Sense cannot seem to accept the use of certain symbols. Please see picture attached.  It is the case for > or =

Any thoughts?  Maybe I can only do this effectively in Data load editor - I will need to get familiar with this if this is the case.

Thanks for your support

[gileswalker]

(.....hey Bruno, just FYI, I have managed to get it working in data load editor, but if you are able to shed any light on the issue I described above in data manager, I would appreciate it.) Thanks.

[Josh_Good]

Hi Giles,

The intention of the Data Manager is to keep things quick and simple.  While more heavy lifting data transformation are intended to be done in the Script Editor.  That said I understand your frustration of having something work in one place and not the other place.  I will raise this with our Products team to investigate.

Josh

Qlik

[gileswalker]

Thanks Josh.  I think long term (with the project I need to deliver) script editor is the only way forward as I'm going to need to do some funky stuff as part of the data loading, and I see your point re: the purpose of the Data Mgr.   Thanks for following this up with the Products team.

[Ian_Crosland]

Hi

Currently the Data Manager Add calculated field GUI does not support the following operands with the If statement (<,>,<=,>=,<>,!=) we have this flagged to fix in future (not too distance future) release.

Thanks for posting this has helped raise the priority for this work

Ian

Qlik

[nicolas_gielen]

Hi Ian,

Sorry for replying to a pretty old post. I just came across the same issue using Qlik Sense Server 3.1.2.

Can you please let me know if this is solved in the meantime, and in which release?

Thanks!

Nicolas

[Ian_Crosland]

Hi

Yes we increased the amount of functions available in the add calculated field wizard in the November release

Thanks

Ian

[nicolas_gielen]

Great, thanks Ian !

Br,

Nicolas