Web Usage Data in Qlikview

soton34 — Mon, 22 Apr 2013 11:41:59 GMT

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

//Outer Loop

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

soton34 — Mon, 29 Apr 2013 10:37:53 GMT

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;

//Outer Loop

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

topic Re: Web Usage Data in Qlikview in QlikView

Web Usage Data in Qlikview

Re: Web Usage Data in Qlikview