Help dimension in set analysis

[#1]

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

[varshavig12]

Can you provide us with some sample data ?

[#3]

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

[sasikanth · Accepted solution]

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

[#5]

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!!!!!

[sasikanth]

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

[#7]

​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.

[#8]

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;