Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi, I have two separate tables.
fields:
table 1) login (datetime), logout (datetime), session (integer)
table 2) date (datetime)
I would like to create a tabular graph where the dimensions have the date field. I would count the sessions where the login field is less than the date field and loguot greater than the date field.
Now I use this expression,
=aggr ( Count ( { < login = {'<=$(=date)'} >*<logout = {'>=$(=date)'} > } session ) , date)
and it only works with a selected date, while I need the full list of dates and the relative count.
Can someone help me?
thank you so much
Try this,
use intervalmatch,
Table1;
Load Login, Logout,Sessio from table1;
Table2:
Load Date from Table2
left join(Table1)
intervalmatch(Date) Load
Login, Logout resident table1;
Expression:
Count(if(not isnull(Login) and not isnull(logout),session))
Can you provide us with some sample data ?
TABLE 1:
login | logout | session |
20/06/2016 06:28 | 20/06/2016 06:45 | 529035 |
20/06/2016 06:42 | 20/06/2016 06:47 | 529036 |
20/06/2016 06:48 | 20/06/2016 06:48 | 529037 |
20/06/2016 06:48 | 20/06/2016 06:49 | 529038 |
20/06/2016 06:49 | 20/06/2016 07:37 | 529039 |
20/06/2016 06:51 | 20/06/2016 07:15 | 529040 |
TABLE 2:
date |
10/06/2016 13:30:00 |
10/06/2016 14:00:00 |
10/06/2016 14:30:00 |
10/06/2016 15:00:00 |
10/06/2016 15:30:00 |
10/06/2016 16:00:00 |
Try this,
use intervalmatch,
Table1;
Load Login, Logout,Sessio from table1;
Table2:
Load Date from Table2
left join(Table1)
intervalmatch(Date) Load
Login, Logout resident table1;
Expression:
Count(if(not isnull(Login) and not isnull(logout),session))
Eureka! It's perfect!
... but ...
it's possible to create a table like my "table 2" (that i've created manually) automatically?
Can I give a specified date and time interval (for example every 5 minutes)?
thank you so much!!!!!
Try this script,
need to modify max_date if you want to get it in a dynamic way
Data:
Load * INLINE [
New_Date
5/4/2014
5/6/2016
];
Let vVal=(5/1440); // 5 min interval
Temp:
Load Max(New_Date) as Maxdate,
Min(New_Date) as MinDate
Resident Data;
LET vMinDate=num(Peek('MinDate',0,'Temp'));
LET vMaxDate=num(Peek('Maxdate',0,'Temp'));
Temp_Calendar:
LOAD
If($(vMinDate)=$(vMinDate) + Iterno()-1, Timestamp($(vMinDate)+$(vVal)) ,Timestamp(peek('Required_Date')+ $(vVal))) as Required_Date
AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);
I tried the script, but unfortunately I have problems. The first date is correct (04/05/2014 00:00:05) but the while statement will stop after 3 days at 18:05:00.
I think to do something useful if I attach this script that generates an automatic datetime to a specific time range. They welcome corrections or additions.
tempDate:
LOAD
'10/06/2016' as MinTimestamp,
'20/06/2016' as MaxTimestamp
\\Resident table;
LET vMinDateCal = num(peek('MinTimestamp',0,'tempDate'))+1;
LET vMaxDateCal = num(peek('MaxTimestamp',-1,'tempDate'));
Temp:
LOAD
TimeStamp($(vMinDateCal) + (RecNo()/6/24) + (IterNo() -1)) as DateTimeStamp //every 10 minutes
AUTOGENERATE ($(vMaxDateCal)-$(vMinDateCal)+1)*144;
DROP Table tempDate;