1 Reply Latest reply: Jun 7, 2011 3:27 AM by Byron Van Wyk RSS

    CrossTable Join/Concatenate

    Byron Van Wyk

      Hi Guys,

       

      I want to join 2 tables with basically the same fields, different values and for a different fiscal year. Below is my script that I am using in the editor. It doesnt seem right though. The reason i say this is because when I put a table box together, I still see all the individual periods appearing as different columns. I was expecting to shorten this to 3 columns only???? Please help

       

      TempData: CrossTable (Periods,Actuals,2)Load "Account Number",
         
      "Fiscal Year",
         
      "Period 1",
         
      "Period 1 YTD",
         
      "Period 2",
         
      "Period 2 YTD",
         
      "Period 3",
         
      "Period 3 YTD",
         
      "Period 4",
         
      "Period 4 YTD",
         
      "Period 5",
         
      "Period 5 YTD",
         
      "Period 6",
         
      "Period 6 YTD",
         
      "Period 7",
         
      "Period 7 YTD",
         
      "Period 8",
         
      "Period 8 YTD",
         
      "Period 9",
         
      "Period 9 YTD",
         
      "Period 10",
         
      "Period 10 YTD",
         
      "Period 11",
         
      "Period 11 YTD",
         
      "Period 12",
         
      "Period 12 YTD",
         
      "Total Year";;SQL SELECT *
      FROM 12345.dbo."vw_Actuals2009";
      CONCATENATE (TempData)LOAD "Account Number",
         
      "Fiscal Year",
         
      "Period 1",
         
      "Period 1 YTD",
         
      "Period 2",
         
      "Period 2 YTD",
         
      "Period 3",
         
      "Period 3 YTD",
         
      "Period 4",
         
      "Period 4 YTD",
         
      "Period 5",
         
      "Period 5 YTD",
         
      "Period 6",
         
      "Period 6 YTD",
         
      "Period 7",
         
      "Period 7 YTD",
         
      "Period 8",
         
      "Period 8 YTD",
         
      "Period 9",
         
      "Period 9 YTD",
         
      "Period 10",
         
      "Period 10 YTD",
         
      "Period 11",
         
      "Period 11 YTD",
         
      "Period 12",
         
      "Period 12 YTD",
         
      "Total Year";SQL SELECT *
      FROM 12345.dbo."vw_Actuals2010";

        • CrossTable Join/Concatenate
          Byron Van Wyk

          Ok, seemed to have figured this one out Whoppee. Does anyone know if there is another way to do this, or is this the method that is used for crosstables and joining?

           

          TempData:Load "Account Number",
             
          "Fiscal Year",
             
          "Period 1",
             
          "Period 1 YTD",
             
          "Period 2",
             
          "Period 2 YTD",
             
          "Period 3",
             
          "Period 3 YTD",
             
          "Period 4",
             
          "Period 4 YTD",
             
          "Period 5",
             
          "Period 5 YTD",
             
          "Period 6",
             
          "Period 6 YTD",
             
          "Period 7",
             
          "Period 7 YTD",
             
          "Period 8",
             
          "Period 8 YTD",
             
          "Period 9",
             
          "Period 9 YTD",
             
          "Period 10",
             
          "Period 10 YTD",
             
          "Period 11",
             
          "Period 11 YTD",
             
          "Period 12",
             
          "Period 12 YTD",
             
          "Total Year";;SQL SELECT *
          FROM 12345.dbo."vw_Actuals2009";


          CONCATENATE (TempData)LOAD "Account Number",
             
          "Fiscal Year",
             
          "Period 1",
             
          "Period 1 YTD",
             
          "Period 2",
             
          "Period 2 YTD",
             
          "Period 3",
             
          "Period 3 YTD",
             
          "Period 4",
             
          "Period 4 YTD",
             
          "Period 5",
             
          "Period 5 YTD",
             
          "Period 6",
             
          "Period 6 YTD",
             
          "Period 7",
             
          "Period 7 YTD",
             
          "Period 8",
             
          "Period 8 YTD",
             
          "Period 9",
             
          "Period 9 YTD",
             
          "Period 10",
             
          "Period 10 YTD",
             
          "Period 11",
             
          "Period 11 YTD",
             
          "Period 12",
             
          "Period 12 YTD",
             
          "Total Year";SQL SELECT *
          FROM 12345.dbo."vw_Actuals2010";

          FinalTable: CrossTable(Period,Actuals,2) LOAD *

          Resident TempData;

          Drop Table TempData;

           

           

          Cheers,

          Byron