2 Replies Latest reply: Dec 17, 2014 10:38 AM by Anand Chouhan RSS

    Matching data from two tables

      Hi All

      I have two tables in data model and I want to eliminate unmatched records between two tables.

      In below example i want to show only ABC,DCS values only and i want to exclude BCD .

       

      how we can do it in script level,pls provide your suggestions.

      table1:

      Key,MAT,value

      1,     ABC,     1

      2,     BCD,    2

      3,      DCS   ,3

       

      Table 2 :

      Key, Comp,value

      1,  ABC, 2

      3, DCS,3

       

       

      output

      Key,MAT,value

      1,     ABC,     3

      3,      DCS   ,6

       

      Thanks

        • Re: Matching data from two tables
          Jeremiah Kurpat

          Hi SwathiRaj P,

               You will need to do joins in the script. I created an example from your data, please find attached dashboard. I used the script:

           

          Table1:

          Load * Inline [

          Key,MAT,value

          1,ABC,1

          2,BCD,2

          3,DCS,3

          ];

           

          Inner Join(Table1)

          Table2:

          Load Key,Comp as MAT, value2 Inline [

          Key, Comp,value2

          1,  ABC, 2

          3, DCS,3

          ];

           

          Final:

          NoConcatenate Load Key, MAT, value+value2 as Value

          Resident Table1;

           

          Drop Table Table1;

           

          Hope this helps!

          • Re: Matching data from two tables
            Anand Chouhan

            Hi,

             

            Another way of the same for doing is

             

             

            Tmp:
            LOAD Key, MAT as Comp, value as Value1;
            LOAD * INLINE [
            Key, MAT, value
            1, ABC, 1
            2, BCD, 2
            3, DCS, 3
            ]
            ;
            Inner Join

            LOAD Key, Comp, value;
            LOAD * INLINE [
            Key, Comp, value
            1, ABC, 2
            3, DCS, 3
            ]
            ;

            LOAD
            Key, Comp,
            Sum(value)+Sum(Value1) as Comm
            Resident Tmp
            Group By Key, Comp;
            DROP Table Tmp;

             

            Regards

            Anand