Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help dimension in set analysis

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

1 Solution

Accepted Solutions
sasikanth
Master
Master

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

View solution in original post

7 Replies
varshavig12
Specialist
Specialist

Can you provide us with some sample data ?

Not applicable
Author

TABLE 1:   

loginlogoutsession
20/06/2016 06:2820/06/2016 06:45529035
20/06/2016 06:4220/06/2016 06:47529036
20/06/2016 06:4820/06/2016 06:48529037
20/06/2016 06:4820/06/2016 06:49529038
20/06/2016 06:4920/06/2016 07:37529039
20/06/2016 06:5120/06/2016 07:15529040

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

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

Not applicable
Author

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

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

Not applicable
Author

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.

Not applicable
Author

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;