4 Replies Latest reply: Sep 16, 2016 8:06 AM by Sunny Talwar RSS

    Resident loads and inner Joins

    Andre Toerien

      I have this sample code, not sure where I am going wrong. I am going wrong somewhere at the Inner Join.

       

      I have attached the sample data.

       

      What I want to do is pivot the 2 Amount columns to the right for each value of Days. So Days  0 = CurrentAmount, 1 = PreviousAmount, 2 = 30Days. This is Ageing Data. Eventually at the end I want to delete the InputTable and just work with the Outputtable which will then have 60 Values for Amount and 60 Values for AmountVat. Our Clients then need this in this format in a qvd file, but that I can do. We could be summing the values for years 1, 2 3 4 etc, but for now I just need to pivot the data. This is just a sample so ignore that it is coming from xls. We get the data from SQL. What is the most effective way of doing this, and it my way is OK what am I missing on the scripting?

       

      InputTable:

      LOAD ID,

           Period,

           AccountNumber,

           Detail1,

           Detail2,

           Amount,

           AmountVAT,

           Days

      FROM

      [C:\Sample\Example Pivoting import.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

      OutputTable:

      NoConcatenate LOAD DISTINCT ID as RID,

           Period as RPeriod,

           AccountNumber as RAccountNumber,

           Detail1 as RDetail1,

           Detail2 as RDetail2

      Resident InputTable;

       

       

      Inner Join InputTable

      Load

      ID as RID,

           Amount as RCurrentAmount,

           AmountVAT as RCurrentAmountVAT

           Where Days = '0' ;

       

      Inner Join InputTable

      Load

      ID as RID,

           Amount as RPreviousAmount,

           AmountVAT as RPreviousAmountVAT

           Where Days = '1' ;

        • Re: Resident loads and inner Joins
          Sunny Talwar

          I think you missed the parenthesis around the table name after the Inner Join keywords

           

          InputTable:

          LOAD ID,

               Period,

               AccountNumber,

               Detail1,

               Detail2,

               Amount,

               AmountVAT,

               Days

          FROM

          [Example Pivoting import.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          OutputTable:

          NoConcatenate LOAD DISTINCT ID as RID,

               Period as RPeriod,

               AccountNumber as RAccountNumber,

               Detail1 as RDetail1,

               Detail2 as RDetail2

          Resident InputTable;

           

          Inner Join (InputTable)

          Load ID as RID,

               Amount as RCurrentAmount,

               AmountVAT as RCurrentAmountVAT

          Resident InputTable

          Where Days = '0' ;

           

          Inner Join (InputTable)

          Load ID as RID,

               Amount as RPreviousAmount,

               AmountVAT as RPreviousAmountVAT

          Resident InputTable

          Where Days = '1' ;

           

          UPDATE: But what are you inner joining on? None of the field names match, wha

            • Re: Resident loads and inner Joins
              Andre Toerien

              Something as silly as that thanks.

               

              Is the Jon not correct on ID?

                • Re: Resident loads and inner Joins
                  Andre Toerien

                  I have it like this now but its adding the columns to InputTable- The Inner Join need to be to OutputTable needs to be OutputTable , Maybe I am not getting this, why?

                   

                  InputTable:

                  LOAD ID,

                       Period,

                       AccountNumber,

                       Detail1,

                       Detail2,

                       Amount,

                       AmountVAT,

                       Days

                  FROM

                  [C:\Users\andreto\Desktop\Example Pivoting import.xlsx]

                  (ooxml, embedded labels, table is Sheet1);

                   

                   

                   

                   

                  OutputTable:

                  NoConcatenate LOAD DISTINCT ID ,

                       Period as RPeriod,

                       AccountNumber as RAccountNumber,

                       Detail1 as RDetail1,

                       Detail2 as RDetail2

                  Resident InputTable;

                   

                   

                  Inner Join (InputTable) -- this needs to bo OutputTable

                  Load

                  ID ,

                       Amount as RCurrentAmount,

                       AmountVAT as RCurrentAmountVAT

                       Resident InputTable

                       Where Days = '0' ;

                    • Re: Resident loads and inner Joins
                      Sunny Talwar

                      Then may be this:

                       

                      InputTable:

                      LOAD ID,

                          Period,

                          AccountNumber,

                          Detail1,

                          Detail2,

                          Amount,

                          AmountVAT,

                          Days

                      FROM

                      [Example Pivoting import.xlsx]

                      (ooxml, embedded labels, table is Sheet1);

                       

                      OutputTable:

                      NoConcatenate LOAD DISTINCT ID as RID,

                          Period as RPeriod,

                          AccountNumber as RAccountNumber,

                          Detail1 as RDetail1,

                          Detail2 as RDetail2

                      Resident InputTable;

                       

                      Inner Join (OutputTable)

                      Load ID as RID,

                          Amount as RCurrentAmount,

                          AmountVAT as RCurrentAmountVAT

                      Resident InputTable

                      Where Days = '0' ;

                       

                      Inner Join (OutputTable)

                      Load ID as RID,

                          Amount as RPreviousAmount,

                          AmountVAT as RPreviousAmountVAT

                      Resident InputTable

                      Where Days = '1' ;