topic Re: Problem with joining tables in QlikView

Problem with joining tables

Thu, 20 Nov 2014 15:07:09 GMT

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

Re: Problem with joining tables

gandalfgray — Thu, 20 Nov 2014 15:14:17 GMT

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

...

Re: Problem with joining tables

Thu, 20 Nov 2014 15:23:15 GMT

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

Re: Problem with joining tables

gandalfgray — Thu, 20 Nov 2014 15:27:08 GMT

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

Re: Problem with joining tables

Thu, 20 Nov 2014 15:32:34 GMT

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;

Re: Problem with joining tables

Peter_Cammaert — Thu, 20 Nov 2014 15:36:28 GMT

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.

Re: Problem with joining tables

Thu, 20 Nov 2014 16:01:01 GMT

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

i've got 12 milions of records in source table