2 Replies Latest reply: Jun 30, 2011 9:21 AM by APS RSS

    help with resident load

      Hi all,

       

      I have the following in my load script:

       

      [table x]:

      Load

      Date,

      Month,

      Year,

      [Year & Month]

      From location x;

       

      [table y]:

      Load

      Date,

      Orders,

      Revenue,

      Salesperson

      From location y;

       

      // I need to left join the next table to table y because [Hours Worked] is at month level not Date level

       

      [table z]:

      Load

      Salesperson,

      [Hours worked],

      [Year & Month],

      [Year & Month] & Salesperson as [Hours Key]

      From location z;

       

      -----------------------------------------------------------------------------------------------

       

      So, I need to add the [Hours Key] dimension to table y to make the left join work but [Year & Month] is in a different table.

       

      I think I need to do a resident load but I'm not sure how this works.

       

      Please can someone advise?

       

      Many thanks.

        • Re: help with resident load

          Hi APS,

          Here is a posible solution I think.

           

          //=======================================

          Table_y:

          //[Orders]:

          LOAD

          Date,

          Orders,

          Revenue,

          Salesperson

          From [Location Y.qvd] (qvd);

          //=======================================

           

          // APS - I need to left join the next table to table y because [Hours Worked] is at month level not Date level

           

          // [Greenee] - I don’t think you should join it, but concatenate it instead.

          Concatenate (Table_y)

           

          LOAD Date(MakeDate(Left(YearMonth,4),Right(YearMonth,2),28),'DD/MM/YYYY') AS [Date]

          //this will Make a date for all the hours worked in that month registered on the 28th of each month

          ,Salesperson

          ,HoursWorked

          ,HoursKey

          //,YearMonth  // you dont need this anymore as it will come from the Calendar

          From [Location Z.qvd] (qvd);

          //=======================================

           

          // Load the dates after

          Table_x:

          //[Calendar]:

          LOAD

          Date,

          Month,

          Year,

          YearMonth

          From [Location X.qvd] (qvd);

          //=======================================

           

           

           

          I have attached a small txt file with the test data that will produce the tables below.

           

          SalesPersonHours2.jpg

           

          SalesPersonHours1.jpg

          Hope this helps

          Good luck

          Regards

          Greenee