3 Replies Latest reply: May 29, 2014 4:50 AM by Jonathan Dienst RSS

    Which joins to which and what type of join?

    Khairul Anuar Azuddin

      Hello guys,

       

      I'm a little bit of blurry on the JOIN part. Say I have two table as pictured below:

       

       

      The table Calendar contains the FiscalYear, FiscalMonth and Days.

       

      Table Revenue might not have all of the revenue of a particular Fiscal Year and FiscalMonth.

       

      To which table must a table join and what type of join must I use to derive the table Revenue1?

       

      Thank you for your kind attention guys. Appreciate any suggestions/opinions and advices on this one. Thanks guys

        • Re: Which joins to which and what type of join?
          Anand Chouhan

          You can concatenate this two tables that is Revenue and Revenue1 otherwise use simple join

           

          1.With Concatenate

          Load Country,FiscalYear,FiscalMonth, Amount

          From Location;

          Concatenate

          Load Country,FiscalYear,FiscalMonth, Days, Amount

          From Location;

           

          2. With Join

          Load Country&FiscalYear&FiscalMonth as %key, Amount

          From Location;

          Join

          Load Country&FiscalYear&FiscalMonth as %key, Days, Amount

          From Location;

          • Re: Which joins to which and what type of join?
            Ashfaq Mohammed

            Hi Khairul,

             

            just to add to anand's answer for concatenate part. add flags even to differentiate table.

             

            1.With Concatenate

            Load Country,FiscalYear,FiscalMonth, Amount, 1 as Flag

            From Location;

            Concatenate

            Load Country,FiscalYear,FiscalMonth, Days, Amount, 2 as Flag.

            From Location;

             

             

            Regards

            ASHFAQ

            • Re: Which joins to which and what type of join?
              Jonathan Dienst

              Hi

               

              Your calendar should have a date field as well,

               

                   Calendar: Date, FiscalYear, FiscalMonth,Day,......

                  

              Then include the revenue date in the revenue table and combine the two revenue tables by concatenating. You may need to add a source code if you need to select data from only one of the revenue tables.

               

              Remove the date fields other than date from the revenue table. Your revenue table is linked on date and will get the derived date fields by association.

               

              HTH

              Jonathan