Conditional Field Calculation in Data Load Script

[jbchurchill]

How do I (in the data load editor) check to see if a field is null or empty and then if it is not empty proceed to subtract another field from the field I was checking? I want to calculate the number of days between open and close dates ([open_date] and [close_date]) for all the records where close_date was populated with a date.

The line I commented out is giving me an error

[JordyWegman]

Hi Jb,

The are two mistakes. Onr reason it gives an error is because you used a comma at the end. And the other is the date formula.

The last field that is called of an table can never have a comma after it!

The date function should work for one date. And Date() is for formatting and Date#() is for interpreting, meaning telling Qlik that this field is a date.

One improvement for your formula. If the field contains a space, it goes wrong. Try to use this instead so it's more safe:

If( trim( close_date) & '' <> '',

null(),

Floor( Date#( close_date, 'YYYYMMDD')) - Floor ( Date#( open_date, 'YYYYMMDD'))

)

The floor is used so it creates a number, 44200 for instance. This is easier subtracting.

The YYYYMMDD is the format I guessed, but you have to fill this in correctly. This is how the open and close date look like!

Jordy 

Climber 

[jbchurchill · Accepted solution]

If(IsNull("close_date"), '', Date(Date#("close_date") - open_date)) as new_days_open,

Actually this did the trick. Note it still has the comma at the end (and still works so I'm not entirely sure that remark is valid). I do still have a semicolon on the line below it. Then again none of the sections had it so I've taken it out and put the semicolon on the same line.
Thanks for the reply - I need to look at the Floor and Ceil functions.

[JordyWegman]

Great, nice solution! Please mark the post as solved.

Jordy

Climber