1 Reply Latest reply: Apr 29, 2013 6:37 AM by Sam Sansome RSS

    Web Usage Data in Qlikview

    Sam Sansome

      Dear All

       

      We have web usage stats available from our server but in a very non user friendly way.

       

      I want to use Qlikview to bring it all together to provide reports to the managers.

       

      Basically data is available on user and date specific pages:

       

      'http://webstats/daily/2013Apr18-2013Apr18/group.simon_smith/group.simon_smith.html

      which contains the WebPage, LengthOfUse, Bytes in a table

       

      I want to have a table of Users:

      Name - Simon Smith, Debbie Jenkins, Jane White etc

      Initials - SS, DJ, JW etc

       

      and a table of Dates

      18/04/2013

      19/04/2013

      20/04/2013 etc

       

      And then get QV to loop through the various combinations to create a master table:

       

      UserName, Initials, Date, WebPage, LengthOfUse, Bytes

       

      25/04/13

      OK so I've got this far by looking at other discussions:

       

      Set vURLStart =  '[http://webstats/daily/';

      Set vURLStart1 = '[http://webstats/daily/2013Apr16-2013Apr16';

      Set vURLStart2 = '[http://webstats/daily/2013Apr15-2013Apr15';

      Set vURLEnd = '.html] (html, ansi, embedded labels, table is @2)';

       

      //Outer Loop

      For Each User in '/group.simon_smith', '/group.debbie_jenkins', '/group.jane_white'

      //Inner Loop

      For Each Date in '2013Apr16', '2013Apr15'

       

      Data:

      LOAD

                  '$(Date)' as Date,

                  '$(User)' as User,

                  [ACCESSED SITE] as Site,

                  BYTES as Bytes,

                  [ELAPSED TIME] as Time

                  FROM $(vURLStart)$(Date)-$(Date)$(User)$(User)$(vURLEnd);

       

      //Inner Loop

      Next

      //Outer Loop

      NEXT

       

      Now to prevent having a very large list of dates I would like Qlikview to reference an Excel spreadsheet

      Something like:

      DatesTable:

      LOAD DATE(@1,'YYYYMMMDD') as Date

      FROM C:\WebDatesOnly.xls (biff, explicit labels, table is Sheet1$);

       

      For Each Date in DatesTable (instead of the comma separated list)

       

      But I just can't get this last bit to work

       

      All simple ideas gratefully received.

       

      Sam

        • Re: Web Usage Data in Qlikview
          Sam Sansome

          Just in case anyone stumbles accross this at a later date - I've fixed it!

           

          Set vURLStart =  '[http://webstats/daily/';

          Set vURLEnd = '.html] (html, ansi, embedded labels, table is @2)';

           

          Set zStartDate = '02/04/13';

          SET zEndDate='08/05/13';

           

          //Outer Loop

          For Each User in '/group.simon_smith', '/group.debbie_jenkins', '/group.jane_white'

          //Inner Loop

          For zi = zStartDate To zEndDate

           

              Let zDate = Date(zi,'YYYYMMMDD');

           

          Set ErrorMode=0;    

          Data:

          LOAD

                          '$(zDate)' as Date1,

                          '$(User)' as User,

                          [ACCESSED SITE] as Site,

                          BYTES as  Bytes,

                          [ELAPSED TIME] as Time

                          FROM $(vURLStart)$(zDate)-$(zDate)$(User)$(User)$(vURLEnd);

           

          //Inner Loop

          Set ErrorMode=1;

          Next

          //Outer Loop

          NEXT

           

          I turned off the ErrorMode to prevent the system telling me each time there wasn't a URL for a User or Date

           

          Otherwise works perfectly!

           

          I wish I had made a note of the posts that helped me acheive this but it just shows that a little hunting can pay off.

           

          Sam