Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a table with field DATE(in the format DD-MM-YYYY, hh:mm:ss) and STATUS field(with values AVAILABLE, OFFLINE)
I have created a straight table, under that i added field as date(DATE), STATUS, and two more columns as measure i.e AVAILABLE AND UNAVAILABLE
FOR AVAILABLE measure i applied expression in the expression editor as :- = if(STATUS = 'AVAILABLE', time(DATE))
FOR OFFLINE measure I applied expression in the expression editor as:- if(STATUS = 'AVAILABLE', time(DATE))
Now I need to add one more measure in the table as TOTAL AVAILABLE DURATION( which means difference between offline time - available time.
for this I used the following expression in the data load editor script
Interval(if(STATUS = 'OFFLINE',time(DATE)) - if(STATUS = 'AVAILABLE',time(DATE))) as total_duration.
But in the front end, in table I am not getting the values for this total_duration column.
Hereby I am attaching the table layout structure
how can i calculate diff between offline and available for a particular date, and show it in separate column as total available duration??
kindly help.
THANKS
try to have them in sigle row if you using datebase
select a.date,a.status,availabe,b.status,b.ofline from
(select date,Status,Available from table1 where status='Available ') a
(select date,Status,offline from table1 where status='Ofline') b
where a.date=b.date
after this your date will be in one row then you can use Available-offline
ok
try to make status in single row
like OnlineStatus Offlinestatus
later add your expressions you need to have single row
select a.date,a.status,b.status from
(select date,Status from table1 where status='Available ') a
(select date,Status from table1 where status='Ofline') b
where a.date=b.date
may be