Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
==========
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;
==========
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.
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
Try to use my solution in Sales table directly !
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.
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.