Problem with joining tables

[#1]

Hello guys,

im making a dashboard and i have to translate SQL statements to Qlikview. I have a table that looks like this:

ID	Date	Churn
111	2014-01-01	2014-01-03
111	2014-01-02	2014-01-03
111	2014-01-03	2014-01-03
111	2014-01-04	2014-01-03
111	2014-01-05	2014-01-03

so what i want to do is to check when Date is bigger that Churn (active column) that's easy and i receive that table:

if(Date>Churn,1,0)

ID	Date	active
111	2014-01-01	0
111	2014-01-02	0
111	2014-01-03	0
111	2014-01-04	1
111	2014-01-05	1

So now i want to check if on the date before the id was active or not so i want to receive such table:

ID	Date	active	active_yesterday
111	2014-01-01	0	0
111	2014-01-02	0	0
111	2014-01-03	0	0
111	2014-01-04	1	0
111	2014-01-05	1	1

To do that i create a table:

ID,

Date-1 as Date,

if(Date-1>Churn,1,0) as active_yeasterday

and i do a left join to original table, but it does not work   I receive something like this:

ID	Date	active	active_yesterday
111	2014-01-01	0	0
111	2014-01-02	0	0
111	2014-01-03	0	1
111	2014-01-04	1	1
111	2014-01-05	1	1

the problem is marked in the red color

pls help

best regards,

Jacek

[gandalfgray]

try to create active and active_yesterday at the same time:

...

if(Date>Churn,1,0) As active,

if(Date-1>Churn,1,0) as active_yesterday

...

[#3]

yep i've tried that but if i do this active_yesterday column looks thesame like active column.

I've also tried to use WHERE Date=Date-1 but then i get null values

[gandalfgray]

Hi Jacek

It's easier to help if you post the actual load script, or at least the relevant parts

I can't understand what you expect to get using: "WHERE Date=Date-1" for instance...

/gg

[#5]

okay here is my code:

CUSTOMER_ACTIVITY_TEMP:

LOAD Date(processing_date) as preceeding_date,

     msisdn_skid,

     churn_30_date,

     churn_90_date

FROM

[***.qvd]

(qvd);

CUSTOMER_ACTIVITY:

LOAD

preceeding_date,

Date(preceeding_date-1) as yesterday,

msisdn_skid,

if(IsNull(msisnd_skid_cb)=0,1,0) as gross_add,

if(Date(churn_30_date)>Date(preceeding_date),1,0) as active_30,

if(Date(churn_30_date)<Date(preceeding_date),1,0) as inactive_30

Resident CUSTOMER_ACTIVITY_TEMP;

LEFT JOIN(CUSTOMER_ACTIVITY)

LOAD

Date(preceeding_date-1) as preceeding_date,

msisdn_skid,

if(Date(churn_30_date)>Date(Date(preceeding_date)-1),1,0) as active_y_30

Resident CUSTOMER_ACTIVITY_TEMP;

DROP Table CUSTOMER_ACTIVITY_TEMP;

[Peter_Cammaert]

The table where you first create active_yesterday probably uses the wrong logic.

If you have a table with days that are flagged as active, you can easily create one with active_yesterday like this:

Active_yesterday:

LOAD ID,

     Date+1 AS Date,            // Tomorrow I'll know that I was active today

     1 AS active_yesterday

RESIDENT Active_today

WHERE active = 1;

Now do a LEFT JOIN of Active_yesterday to Active_today.

[#7]

i think that's not efficient way, because my computer died

i've got 12 milions of records in source table