9 Replies Latest reply: Mar 16, 2018 9:38 PM by Prashant Sangle RSS

    Should I Use Join/ Concatenate/Keep to create a combined tabled??

    Aman Jain

      I have an interesting use case as you see below.

      Need is to combine the File 1 and File 2 to generate a final table as shown below. Please refer to the attached file for more clarity.

       

      There are some conditions when making the final table..

      1. When Measure 1 is present , then only the Final Reason is valid. Therefore Material E has a Final Reason- N/A.
      2. When Reason Code is 1026 or 1046 in File 1 , then we go to the File 2 to find out the Final Reason from the Text Column.
      3. In File 2, Material X is not present in File 1 so it is not considered in the final table.
      4. In general if a material is present in File 1 with the Reason Code 1026/1046 only then you go to File 2. The Amount in File 2 for a particular material is mostly a subset of Measure 1. Example Material A has 5$ in File 1 as Measure 1 and the total of Material A in file 2 is 5$. So the total amount in File 2 <= Measure 1.
      5. Total measure = Measure 1+ Measure 2+ Measure 3+Measure 4

       

       

       

                  

      File 1 File 2
      Material Plant CodeReason CodeMeasure 1Measure 2Measure 3Measure 4Total Measure Material Plant CodeReason CodeTextAmount
      A10110265 5 A1011026M3032.5
      B102104610 10 A1011026M3042.5
      C103M2013 3 B1021046S1015
      D104M1012 3 5 B1021046S1022.5
      E105M301 11 B1021046X1031.5
      Totals 2003124 X1041026M1012
      X1041026M1023
      Final Table
      Material Plant CodeReason CodeFinal ReasonMeasure 1Measure 2Measure 3Measure 4AmountTotal Measure
      A1011026M3035 2.55
      A1011026M3045 2.55
      B1021046S10110 510
      B1021046S10210 2.510
      B1021046X10310 1.510
      C103M201M2013 33
      D104M101M1012 3 25
      E105M301M301 1-1
      Totals 200311924
      or
      Material Plant CodeReason CodeFinal ReasonMeasure 1Measure 2Measure 3Measure 4AmountTotal Measure
      A1011026M3035 2.55
      A1011026M3045 2.55
      B1021046S10110 510
      B1021046S10210 2.510
      B1021046X10310 1.510
      C103M201M2013 33
      D104M101M1012 3 25
      E105M301N/A 1-1
      Totals 200311924

       

      I am stuck down how to create this table.

      I had tried to use Join, concatenate etc. but may be I am not using it in the right way.

      Any help is appreciated.