7 Replies Latest reply: Jun 21, 2016 7:31 AM by gabriele giovannelli RSS

    Help dimension in set analysis

    gabriele giovannelli

      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

        • Re: Help dimension in set analysis
          Varsha Vig

          Can you provide us with some sample data ?

            • Re: Help dimension in set analysis
              gabriele giovannelli

              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
            • Re: Help dimension in set analysis
              sasi k

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

                • Re: Help dimension in set analysis
                  gabriele giovannelli

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

                    • Re: Help dimension in set analysis
                      sasi k

                      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);
                      
                        • Re: Help dimension in set analysis
                          gabriele giovannelli

                          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.

                          • Re: Help dimension in set analysis
                            gabriele giovannelli

                            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;