Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
...
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
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
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;
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.
i think that's not efficient way, because my computer died
i've got 12 milions of records in source table