QlikView App Dev

[soton34]

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

[soton34 · Accepted solution]

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