6 Replies Latest reply: Feb 16, 2016 2:26 AM by Hasvine Dhurmea RSS

    Field matching

    Hasvine Dhurmea

      Hello All,

       

      I have two table:

       

      TableA:

      Load  A,

               B,

               C

               Code;

      SQL Select *

      From SourceA

       

      Info in table A:

       

      ABC
      AP001AP002AP001
      AP003AP004AP005
      AP005AP001AP006
      AP010AP007AP008

       

       

      TableB contains the meaning of the the "APXXX" codes.

       

      TableB,

      Load * inline

       

      [Code , Codification , Recomendation , Top5

      AP001, Blue, Black , Yellow

      AP002, Green, Pink, Pink

      AP003, Brown, Red, Blue

      ETC....................]

       

       

      I need to create a table like the below:

      ACodificationRecommendationTop5BCodificationRecommendationTop5CCodification
      AP001BlueBlackYellowAP002GreenPinkPinkAP001Blue
      AP002GreenPinkPink
      AP003BrownRedBlue

       

      Any idea on how to create it?

       

      Thanks,

        • Re: Field matching
          Gowtham Kesavan

          use left join

          • Re: Field matching
            balraj ahlawat

            you can use Left or Inner join based on requirement, something  like this?

             

            TableA:

            Load  A as Code,

                     B,

                     C;

            //         Code;   //Comment it

            SQL Select *

            From SourceA

             

            left join(TableA) or inner join(TableA)


            TableB,

            Load * inline

             

            [Code , Codification , Recomendation , Top5

            AP001, Blue, Black , Yellow

            AP002, Green, Pink, Pink

            AP003, Brown, Red, Blue

            ETC....................]

            • Re: Field matching
              Pradip Sen

              Could you upload "Code" field data in Table A?

              • Re: Field matching
                Avinash R

                how does that table A related to table B i.e. based on the code ? because even the column A,B,C also has the similar values ....do you need to map for each column values or you need to map with only one field

                • Re: Field matching
                  Jonathan Dienst

                  Here's one attempt - Load like this:

                   

                  TableA:
                  Load A, B, C
                  Inline
                  [ 
                  A, B, C
                  AP001, AP002, AP001
                  AP003, AP004, AP005
                  AP005, AP001, AP006
                  AP010, AP007, AP008
                  ];
                  
                  
                  TableB:
                  Load * 
                  Inline
                  [
                  Code , Codification , Recomendation , Top5
                  AP001, Blue, Black , Yellow
                  AP002, Green, Pink, Pink
                  AP003, Brown, Red, Blue
                  ];
                  
                  
                  T1:
                  LOAD A As Code,
                    'A' As Group,
                    RecNo() As Row
                  Resident TableA;
                  
                  
                  LOAD B As Code,
                    'B' As Group,
                    RecNo() As Row
                  Resident TableA;
                  
                  
                  LOAD C As Code,
                    'C' As Group,
                    RecNo() As Row
                  Resident TableA;
                  
                  
                  Drop Table TableA;
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  

                   

                  Next, create a pivot table with Row and Group as dimensions and the following expressions:

                       Code: =MinString(Code)

                       Codification = MinString(Codification)

                       Recomendation: =MinString(Recomendation)

                       Top5: =MinString(Top5)

                   

                  Drag the Group Dimension to the top of the chart and make the first column as small as possible. Enter a single space as the label for both dimensions and make the null symbol a space. With a little custom formatting:

                  t1.png