Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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?

Tags (1)
6 Replies
mov
Esteemed Contributor III

Re: Calculation during load from resident table data in memory

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

Re: Calculation during load from resident table data in memory

Will this give minutes?

mov
Esteemed Contributor III

Re: Calculation during load from resident table data in memory

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

Re: Calculation during load from resident table data in memory

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

mov
Esteemed Contributor III

Re: Calculation during load from resident table data in memory

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')

andrea_reyes
New Contributor III

Re: Calculation during load from resident table data in memory

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

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