Data Load Editor - operations with a new field

[BZ]

Hello,

I have a relatively simple set of data from a CSV file which I'm loading in Qlik Sense. There are 4 date fields: Contract Start Date, Contract End Date, Item Start Date and Item End Date.

It is possible that either the Contract Start or the Contract End Date fields could be empty. This is why I would like to create 2 new mixed columns/fields and I have the following in the Data Load Editor:

if(isnull([Contract Start Date]),[Item Start Date],[Contract Start Date]) AS [New Start Date],
If(IsNull([Contract End Date]),[Item End Date],[Contract End Date]) AS [New End Date]

So far so good. However, what I would like afterwards is to have a Term field which should be the difference between New End Date and New Start Date in whole months.

If I try to type this in the Data Load Editor:

Interval([New End Date]-[New Start Date],'m') AS [New Term],

I get an error:

The following error occurred:

Field 'New End Date' not found

The error occurred here:

?

Could you help me sort this out, please?

Thank you!

[Jacek · Accepted solution]

Please use Preceding Load for that, then you can use new names of fields:

Load *,
Interval([New End Date]-[New Start Date],'m') AS [New Term];

Load
Contract Start Date,
Contract End Date,
Item Start Date,
Item End Date,

if(isnull([Contract Start Date]),[Item Start Date],[Contract Start Date]) AS [New Start Date],
If(IsNull([Contract End Date]),[Item End Date],[Contract End Date]) AS [New End Date]

From ....

[timpoismans]

Hi

Try the following:

Randomtablename:
Load
*,
Interval([New End Date]-[New Start Date],'m') AS [New Term];
Load
...,
if(isnull([Contract Start Date]),[Item Start Date],[Contract Start Date]) AS [New Start Date],
If(IsNull([Contract End Date]),[Item End Date],[Contract End Date]) AS [New End Date]
FROM .... 

Dots are to be filled in by you, don't know the structure of the table or where you are loading from 🙂

[BZ]

Thank you very much, Jacek!