Make sums in load script

[Anonymous]

Hello,

Consider the following table:

Date	UserID	Pageviews
01/01/2015	ABC	1
02/01/2015	ABC	2
03/01/2015	DEF	2
03/01/2015	ABC	1

Is it possible, in the load script, the create an additional column, that indicates "YES" if the total number of pageviews on the day in question >= 3 ?

So you would end up with this:

Date	UserID	Pageviews	>= 3 pageviews
01/01/2015	ABC	1	NO
02/01/2015	ABC	2	YES
03/01/2015	DEF	2	NO
03/01/2015	ABC	1	YES

So basically, for the user ABC, on 02/01/2015, he reached 3 pageviews. From that moment onwards, the column ">=3 pageviews" should become YES for that user.

For the user DEF, on 03/01/2015, he did not reach 3 so far, so it should be "NO" still.

Is this even possible?

I'd prefer to have this in the load script as I want to filter on this.

Any help would be greatly appreciated.

Kind regards,

Christophe

[maxgro · Accepted solution]

source:

LOAD Date,

     UserID,

     Pageviews

FROM

[http://community.qlik.com/thread/156254]

(html, codepage is 1252, embedded labels, table is @1);

final:

load

  *,

  if(CumPageviews>=3, 'YES','NO') as PageviewsGE3;

load

  UserID,

  Date,

  Pageviews,

  if(Peek(UserID)<>UserID, Pageviews, Peek(CumPageviews)+Pageviews) as CumPageviews

Resident source

Order By UserID, Date;

DROP Table source;

[Anonymous]

Amazing ! Thanks, that worked perfectly !!