topic Re: Calculation during load from resident table data in memory in QlikView

Calculation during load from resident table data in memory

Mon, 22 Jul 2013 19:33:10 GMT

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?

Re: Calculation during load from resident table data in memory

Anonymous — Mon, 22 Jul 2013 19:49:44 GMT

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

Re: Calculation during load from resident table data in memory

Mon, 22 Jul 2013 19:55:15 GMT

Will this give minutes?

Re: Calculation during load from resident table data in memory

Anonymous — Mon, 22 Jul 2013 19:58:44 GMT

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

Re: Calculation during load from resident table data in memory

Mon, 22 Jul 2013 20:02:53 GMT

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

Re: Calculation during load from resident table data in memory

Anonymous — Mon, 22 Jul 2013 20:09:45 GMT

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

Re: Calculation during load from resident table data in memory

Anonymous — Mon, 22 Jul 2013 20:12:30 GMT

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

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