5 Replies Latest reply: May 29, 2013 3:50 AM by Dimitris Charalampou RSS

    help in outer join

      Hi there,

       

      I have a table that contains the customerid, the monthyear combination and the number of transactions that he performed

       

      customer idmonthyearnumber of transactions
      1June-20111
      1January-20123
      1January-20135
      1February-20123
      1May-20137
      2June-20124
      2August-20126
      2May-20139
      3August-201243
      3April-201321
      3May-201334

       

      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.

        • Re: help in outer join
          j i

          table:

          load * from yourTable;

           

          table1:

          outer join (table)

          load * Resident table;

           

          Drop table table;

            • Re: help in outer join

              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.

            • Re: help in outer join

              Hi,

               

              You can try this,

               

              A:

              Load ..,

                      Monthyear,

                      ..

              From Calendar;

               

              JOIN(Monthyear)

              Load ..,

                      Monthyear,

                      ..

              From Transactions;

               

              Thanks,

                • Re: help in outer join

                  Hi there,

                   

                  Unfortunately this does not work for me.

                   

                  if for customerid =1 i have the following data:

                   

                  customer idmonthyearnumber of transactions
                  1June-20111
                  1January-20123
                  1January-20135
                  1February-20123
                  1May-20137

                  the result i want to achieve should be:

                   

                  customer idmonthyearnumber of transactions
                  1June-20111
                  1July-20110
                  1August-20110
                  1September-20110
                  1October-20110
                  1November-20110
                  1December-20110
                  1January-20123
                  1February-20123
                  1March-20120
                  1April-20120
                  1May-20120
                  1June-20120
                  1July-20120
                  1August-20120
                  1September-20120
                  1October-20120
                  1November-20120
                  1December-20120
                  1January-20135
                  1February-20130
                  1March-20130
                  1April-20130
                  1May-20137

                   

                  Thank you.