topic calculated column used in same resident table or not in QlikView

calculated column used in same resident table or not

Thu, 25 Sep 2014 17:04:09 GMT

I have one transaction QVD like

load

date_enter,

date_close

....,

transaction.qvd

in that QVD I am calculating the duration. duration is in minutes format

SLR:

load

interval(date_enter - date_close,'mm') as duration,

if(duration <= 15,'15 minutes',

if(duration >15 and duration<=60 '1 hour',)) as category

resident transaction;

drop table SLR;

my question is duration is calculation syntax is correct or not based on minutes and other question is the calculated column directly used in the same table or not plz tell me anyone....urgent

Re: calculated column used in same resident table or not

anbu1984 — Thu, 25 Sep 2014 17:34:27 GMT

You cannot use calculated column directly in the same table.

SLR:

Load *,if(duration <= 15,'15 minutes',

if(duration >15 and duration<=60 '1 hour',)) as category;

Load

interval(date_enter - date_close,'mm') as duration

resident transaction;

Re: calculated column used in same resident table or not

geert_gelade — Thu, 25 Sep 2014 17:46:13 GMT

I think you can't compare the result of the interval function in minutes with the numer 15. The result will display in minutes but the value is in days. So 15 minutes displayed = 15/(60*24) as value.

Maybe it's better to calculate the difference as (date_enter - date_close)*24*60

You also have to use a preceding load when you want to re-use a calculated field.

You can't use the newly created field "duration" in the same load. By using a preceding load you can:

SLR:

load *,

if(duration <= 15,'15 minutes',

if(duration >15 and duration<=60, '1 hour',)) as category

load

(date_enter - date_close)*24*60 as duration,

resident transaction;

Re: calculated column used in same resident table or not

Thu, 25 Sep 2014 17:48:24 GMT

Hi,

You can try something like below

SLR:

load

interval(Date#(' date_enter','M/D/YYYY')-Date#('date_close','M/D/YYYY'),'mm') as Duration

resident transaction;

SLR2:

Load

if(duration <= 15,'15 minutes',

if(duration >15 and duration<=60 '1 hour',)) as category

resident SLR;

Hope it helps!

Kiru