Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I have a table that contains the customerid, the monthyear combination and the number of transactions that he performed
customer id | monthyear | number of transactions |
1 | June-2011 | 1 |
1 | January-2012 | 3 |
1 | January-2013 | 5 |
1 | February-2012 | 3 |
1 | May-2013 | 7 |
2 | June-2012 | 4 |
2 | August-2012 | 6 |
2 | May-2013 | 9 |
3 | August-2012 | 43 |
3 | April-2013 | 21 |
3 | May-2013 | 34 |
and i want to get a list with the customerid and all the monthyear combinations starting from June 2011 along with their number of transactions, with the value of '0' where the customer did not perform any.
Thank you.
table:
load * from yourTable;
table1:
outer join (table)
load * Resident table;
Drop table table;
Hi there,
when i run your code it results in no tables, because of the drop table command.
Without the drop table command it just returns the original table.
To be more precise, i want to have 23 records for each customer , one for each month starting June-2011 untill May-2013. If the customer has performed transactions in one of these months it should have that value in the "number of transactions" field.
Other wise it should be equal to zero.
I also have a table with all the month year combinations starting from June-2011 untill May-2013.
Thank you.
Hi,
You can try this,
A:
Load ..,
Monthyear,
..
From Calendar;
JOIN(Monthyear)
Load ..,
Monthyear,
..
From Transactions;
Thanks,
Hi, sorry but why 23? If you have 3 clients and 8 distinct Month-Year, that would be 24. Is this what you want?
Hi there,
Unfortunately this does not work for me.
if for customerid =1 i have the following data:
customer id | monthyear | number of transactions |
1 | June-2011 | 1 |
1 | January-2012 | 3 |
1 | January-2013 | 5 |
1 | February-2012 | 3 |
1 | May-2013 | 7 |
the result i want to achieve should be:
customer id | monthyear | number of transactions |
1 | June-2011 | 1 |
1 | July-2011 | 0 |
1 | August-2011 | 0 |
1 | September-2011 | 0 |
1 | October-2011 | 0 |
1 | November-2011 | 0 |
1 | December-2011 | 0 |
1 | January-2012 | 3 |
1 | February-2012 | 3 |
1 | March-2012 | 0 |
1 | April-2012 | 0 |
1 | May-2012 | 0 |
1 | June-2012 | 0 |
1 | July-2012 | 0 |
1 | August-2012 | 0 |
1 | September-2012 | 0 |
1 | October-2012 | 0 |
1 | November-2012 | 0 |
1 | December-2012 | 0 |
1 | January-2013 | 5 |
1 | February-2013 | 0 |
1 | March-2013 | 0 |
1 | April-2013 | 0 |
1 | May-2013 | 7 |
Thank you.