Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

count days between a buy

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

DateCookieSessionFlagDays
2014-04-15AB0
2014-04-19AC4
2014-04-25AD6

How is this possible?

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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;

==========

View solution in original post

6 Replies
MK_QSL
MVP
MVP

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
Master III
Master III

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.

Not applicable
Author

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
MVP
MVP

Try to use my solution in Sales table directly !

Not applicable
Author

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
MVP
MVP

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.