count days between a buy

[#1]

Hello

I have a problem im not able to solve:

I want to Count how many days there have been before a user last bought something. So the dataset would look like this:

And i want to add the FlagDays column

Date	Cookie	Session	FlagDays
2014-04-15	A	B	0
2014-04-19	A	C	4
2014-04-25	A	D	6

How is this possible?

[MK_QSL · Accepted solution]

Use below script...

==========

Temp:

Load * Inline

[

  Date, Cookie, Session

  2014-04-15, A, B

  2014-04-19, A, C

  2014-04-25, A, D

];

NoConcatenate

Final:

Load

  Date,

  Cookie,

  Session,

  IF(Cookie = Previous(Cookie), Date - Previous(Date), 0) as FlagDays

Resident Temp

Order By Cookie, Date;

Drop Table Temp;

==========

[sujeetsingh]

use peek and iteration as

load

Date,Cookie,session

,interval(date-Previous(Date),'dd') as FlagDays

resident Table1

while (cokie<> previous(cookie);

\

Try something like this.

[#4]

Sorry i think i deleted the post.

I have another question.

My data model is like this:

Hej:

Load

Cookie,

Date,

Sales,

Session,

Product

from QVD

Then i make a lot of temptables

like

TempDayInterval

Load distinct

Cookie as CookieTemp

,Session as SessionTemp

,Date as DateTemp

Resident Hej;

Then i make my new headtable

NoConcatenate

Sales:

Load

Cookie,

Date,

Sales,

Session,

Product,

if(???????????????????????????????????????) as FlagDay

Resident Hej;

How do i make my if statement Down here when my data model looks like this?

Sometimes i use if(exist(TempSession,Session), whatever......) as FlagWhatever

[MK_QSL]

Try to use my solution in Sales table directly !

[#6]

I already do that but the problem in the sales table is that i can only sort by date and not by cookie (because they some other calculations will be wrong)

- I just wanted to know if it was possible to do it from the temp table.

[MK_QSL]

I don't have idea about your use of Temp Table... In this case, if you prepare some sample data file or apps, would be helpful to give you exact answer.