help in outer join

[#1]

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.

[jvitantonio]

table:

load * from yourTable;

table1:

outer join (table)

load * Resident table;

Drop table table;

[#3]

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.

[#4]

Hi,

You can try this,

A:

Load ..,

        Monthyear,

        ..

From Calendar;

JOIN(Monthyear)

Load ..,

        Monthyear,

        ..

From Transactions;

Thanks,

[jvitantonio]

Hi, sorry but why 23? If you have 3 clients and 8 distinct Month-Year, that would be 24. Is this what you want?

[#6]

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.