Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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