topic count days between a buy in QlikView

count days between a buy

Tue, 29 Apr 2014 08:30:40 GMT

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?

Re: count days between a buy

MK_QSL — Tue, 29 Apr 2014 08:36:37 GMT

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;

==========

Re: count days between a buy

sujeetsingh — Tue, 29 Apr 2014 08:40:22 GMT

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.

Re: count days between a buy

Tue, 29 Apr 2014 09:15:59 GMT

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

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

Re: count days between a buy

MK_QSL — Tue, 29 Apr 2014 09:20:26 GMT

Try to use my solution in Sales table directly !

Re: count days between a buy

Tue, 29 Apr 2014 09:25:53 GMT

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.

Re: count days between a buy

MK_QSL — Tue, 29 Apr 2014 09:32:11 GMT

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.