Running a Loop and Creating Groups - Web Analytics

[#1]

I've looked around briefly for an answer to this, but it doesn't look like it has been asked.

I want to use QlikView for web analytics. My tables are FuseActions and People.

The tables look like this:

FuseActions:

LOAD Fuse_Action_ID,

     User_ID,

     Fuse_Action,

     Date (Date) as Date,

     Day (Date) as Day,

     Month (Date) as Month,

     Year (Date) as Year,

     Time

People:

LOAD User_ID,

     Company_ID,

     Company_Name,

I want to be able to layout "Paths" that a user follows on our websites. I would like to turn these "Paths" into groups. Fuse_Action represents a click to another page in our website. So each "Path" would be made up of several Fuse_Actions until we return to the homepage or exit the website. My general thinking is to have a sort by User_ID, followed by a sort by Time. With things sorted this way, I like to run a loop that would group all Fuse_Actions from the homepage until the next time the homepage comes up and be able to group that as a "Path." I am a new user and do not have any idea how I would write this in QlikView or if it is even possible.

[swuehl · Accepted solution]

Maybe you could do it like

LOAD *,

if(peek(User_ID) <> UserID or Fuse_Action='Homepage', rangesum(peek(Path),1),rangesum(peek(Path))) as Path

resident FuseActions order by User_ID, Time;

This should enumerate each path in a new field Path over all User_IDs.

Hope this helps,

Stefan

[#3]

Thanks Stefan, it worked perfectly.

Now another question:

There are some repeating "Paths"  in the list. How could I identify each duplicate "path" as a uniqe path and display a count to show how many times that path occurs.

[swuehl]

You could add

left join (FuseActions) LOAD

concat(Fuse_Action,'-',Fuse_Action_ID) as FQPath,

Path

Resident FuseActions group by Path;

and then do a distinct count on FQPath.

Hope this helps,

Stefan

edit:

You could also use an autonumber() around the concat to create a number instead of the lenghty text (or both), could be more performant. And you don't necessarily need the left join, you could also keep the tables just linked.