Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Tags (2)
1 Solution

Accepted Solutions
sasikanth
Valued Contributor III

Re: Help dimension in set analysis

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

7 Replies
varshavig12
Valued Contributor

Re: Help dimension in set analysis

Can you provide us with some sample data ?

Not applicable

Re: Help dimension in set analysis

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
Valued Contributor III

Re: Help dimension in set analysis

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

Re: Help dimension in set analysis

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
Valued Contributor III

Re: Help dimension in set analysis

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

Re: Help dimension in set analysis

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

Re: Help dimension in set analysis

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;