3 Replies Latest reply: Oct 11, 2017 8:05 AM by Andrew Walker RSS

    JOIN, KEEP OR CONCATENATE?

    Pakalapati Sai Kumar

      Hello All,

       

      I have data from two tables.

       

      Table1:

      Product          Sales
      A10
      B20
      C30
      D40

       

       

      Table 2:

      Product          Sales
      A50
      B30
      C45
      T23
      Z60

       

       

      Expected Result:

      Product          Sales
      A60
      B50
      C75
      D40

       

       

      Basically, i need to show only the Products that are only in Table 1 along with the sum of sales from Table 2.

      Also, these are around 6 M records so would also be good to have performance in mind.

       

       

      Thanks!!

        • Re: JOIN, KEEP OR CONCATENATE?
          Clever Anjos

          Basically

           

          T:

          Load Product, Sales

          From yourtable1qvd(qvd);

           

           

          concatenate(T)

          Load Product, Sales

          From yourtable2qvd(qvd)

          Where exists(Product);

           

           

          Table:

          noconcatenate Load // maybe can take long time (group by operations are very expensive)

            Product,

            Sum(Sales) as Sales

          Resident T

          Group by Product;

          Drop table T;

          • Re: JOIN, KEEP OR CONCATENATE?
            Anil Babu

            Here you go

             

            Table1:

            LOAD * Inline [

            Product        ,   Sales

            A, 10

            B ,20

            C, 30

            D, 40

            ];

            Concatenate(Table1)

            Table2:

            LOAD * Inline [

            Product        ,   Sales

            A, 50

            B, 30

            C, 45

            T, 23

            Z, 60

            ] Where Exists(Product);

             

            Final:

            NoConcatenate

            LOAD Product,

              Sum(Sales) as Sales

            Resident Table1

            Group by Product;

            Drop table Table1;

            • Re: JOIN, KEEP OR CONCATENATE?
              Andrew Walker

              Hi,

              Try

              Data:

              Load Product, Sales From Table1;

               

              Concatenate(Data)

              Load Product, Sales From Table1

              Where Exists(Product);


              Then in the UI a straight table with dimension Product and expression Sum(Sales) or instead in script:


              Noconcatenate

              [Aggregated Data]:

              Load

              Product,

              sum(Sales) as Sales

              Resident Data Group by Product;


              Drop table Data;



              Regards


              Andrew