Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with joining tables

Hello guys,

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

IDDateChurn
1112014-01-012014-01-03
1112014-01-022014-01-03
1112014-01-032014-01-03
1112014-01-042014-01-03
1112014-01-052014-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)

IDDateactive
1112014-01-010
1112014-01-020
1112014-01-030
1112014-01-041
1112014-01-051

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

IDDateactiveactive_yesterday
1112014-01-0100
1112014-01-0200
1112014-01-0300
1112014-01-0410
1112014-01-0511

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:

IDDateactiveactive_yesterday
1112014-01-0100
1112014-01-0200
1112014-01-0301
1112014-01-0411
1112014-01-0511

the problem is marked in the red color

pls help

best regards,

Jacek

6 Replies
gandalfgray
Specialist II
Specialist II

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

...

Not applicable
Author

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
Specialist II
Specialist II

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

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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.

Not applicable
Author

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

i've got 12 milions of records in source table