Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
soton34
New Contributor III

Web Usage Data in Qlikview

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

1 Solution

Accepted Solutions
soton34
New Contributor III

Re: Web Usage Data in Qlikview

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

1 Reply
soton34
New Contributor III

Re: Web Usage Data in Qlikview

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

Community Browser