Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculation during load from resident table data in memory

Here is the situation.... I am about to load data from a resident table, with code like....

Table:

LOAD

[Arrival DateTime],

[Screen DateTime],

[Depart DateTime]

RESIDENT Temp_ET;

I want to do a date difference in minutes between [Arrival DateTime] and [Screen DateTime] as well during the load...BUT if one of them is null then I want to return null obviously. Also, if for some reason in the data, the [Screen DateTime] is earlier than the [Arrival DateTime], then I also want to return null instead of a negative value. How can I add this calculation into the load?

6 Replies
Anonymous
Not applicable
Author

Brian, this should work (if I got the rules right):

Table:
LOAD
if([Arrival DateTime] < [Screen DateTime]
and len(trim([Arrival DateTime]) >0
and len(trim([Screen DateTime]) >0,
[Screen DateTime] - [Arrival DateTime]) as Difference,
[Arrival DateTime],
[Screen DateTime],
[Depart DateTime]
RESIDENT Temp_ET;

Regards,

Michael

Not applicable
Author

Will this give minutes?

Anonymous
Not applicable
Author

It will give days.  But you can use interval function for a better formatting:

if([Arrival DateTime] < [Screen DateTime]

and len(trim([Arrival DateTime]) >0

and len(trim([Screen DateTime]) >0,

interval([Screen DateTime] - [Arrival DateTime], 'DD hh:mm') as Difference,

So you get it in days, hours, and minutes

Not applicable
Author

This is very helpful Michael, but I actually want the actual data to just be the number of minutes as an integer...

Anonymous
Not applicable
Author

In this case, you can multiply the difference the days by number of minutes in a day, 24*60, no need for interval:

([Screen DateTime] - [Arrival DateTime]) * 24 * 60

I think that interval can be used too, but use only minutes:

interval([Screen DateTime] - [Arrival DateTime], 'mm')

Anonymous
Not applicable
Author

replace the format ' 'DD hh:mm'' by 'mm':

interval([Screen DateTime] - [Arrival DateTime], 'mm')