4 Replies Latest reply: Jun 16, 2011 2:43 PM by Steve Dark RSS

    Join two tables with a field!

      Hi All

       

      May I know how can I join "Login" and "Calendar" table on LoginDate. Please help me out!

       

      Login:
      
      Load LoginDate, UserID, Application,'1' as Remark Resident Login1;
      
      Calendar:
      
      LOAD LoginDate, 
           Year, 
           Quarter, 
           Month, 
           Week
      Resident table;
      
        • Re: Join two tables with a field!

          use the sintaxe "JOIN" (INNER / OUTER/ LEFT / RIGHT)

           

          Login:

          Load LoginDate, UserID, Application,'1' as Remark Resident Login1;

           

          Calendar:
          INNER JOIN(Login)
          LOAD LoginDate,
               Year,
               Quarter,
               Month,
               Week
          Resident table;

          • Re: Join two tables with a field!

            Hi

             

            I think below script joins the Login and Calendar field with the common field 'LoginDate' between this two tables. Please correct me if I am wrong!

             

            Login:
            Load LoginDate, UserID, Application,'1' as Remark Resident Login1;
            
            Calendar:
            JOIN(Login)
            LOAD LoginDate, 
                 Year, 
                 Quarter, 
                 Month, 
                 Week
            Resident table; 
            
            


            Thanks

             

            Attitude

              • Re: Join two tables with a field!

                Yes, the field with some nema are the keys between tables.

                 

                If you have a compost key, you need to concatenate two or N filed in just a field.

                 

                Login:

                Load LoginDate & UserID AS PK

                      , Application

                      ,'1' as Remark

                Resident Login1;


                Calendar:
                INNER JOIN(Login)
                LOAD LoginDate & UserID AS PK
                        , Year
                        , Quarter
                        , Month
                        , Week
                Resident table;

                 

                 

                If you not indicate INNER  before JOIN see in your example:

                 

                JOIN(Login)

                 

                The script assume the sintaxe OUTER.

                 

                OUTER JOIN (Login) = JOIN (Login)

              • Re: Join two tables with a field!
                Steve Dark

                From what I can see you do not need to join and your original script will 'associate' the two LoginDate fields as they have the same name.  This should then behave as you want it to.  Check the Table Viewer (Ctrl+T) to see if the association has been succesful (names need to be identical, case and all).

                 

                If all the calendar fields can be derived from the date you are better off working them out and not having a join at all, thusly:

                 

                Login:
                Load

                     date(LoginDate, 'DD MMM YYYY') as LoginDate,

                     Year(LoginDate) as Year,

                     'Q' & (ceil(Month(Date) / 3)) as Quarter,

                     Month(LoginDate) as Month,

                     week(LoginDate) as Week,

                     date(monthstart(Date), 'MMM-YY') as [Month Year],

                     UserID,

                     Application,

                     '1' as Remark

                Resident Login1;

                 

                This should be much more effiecient - particularly when you get up to larger data volumes.  The script assumes that the field LoginDate is a valid date field - otherwise you will need to nest a Date# function to convert it first.

                 

                - Steve