3 Replies Latest reply on Oct 14, 2016 10:03 AM by Wes Reneau

    Duplicated Data

    james.raikes

          Hi,

       

      I am still getting to grips with joining workbooks and I am experiencing a data duplication issue and a basic table example is below the data is joined tables are $ amount and instrument type on account. Effectively for each instrument type there is only 1 type of $ data applicable to a system type but because the value is in three different columns it duplicates the numbers across each column. How do I set a column $A only applies to instrument Type Z and this amount is only on one instrument line? any suggestions welcome.

      Account
      Instrument
      $A$B$C
      BE50
      BR50
      cZ50
      cZ75
      cE100
      bQ8
      dQ9
        • 1. Re: Duplicated Data
          Wes Reneau

          Hi James,

           

          I'm a little confused by your example. If you could post examples of table A and table B along with your join type and conditions it would help me and forum troubleshoot.

          Thanks,

          -Wes

          • 2. Re: Duplicated Data
            james.raikes

            Hi Wes,

             

            Below is a screen shot of what I am looking at once I have joined. This is one line for an account. The asset Bucket is a group of system and the system type is the individual system name with the product s just another description. The issue I have is the numbers are in one sheet and the products are related to the system across the asset bucket field and this is then duplicating the total for the account bucket line on each system type line. Would like the amount to only show once on the system line. Would like to send work book but not possible. Will see if I can replicate using the superstore data.

            Capture Tab.jpg

            • 3. Re: Duplicated Data
              Wes Reneau

              If I understand correctly, try creating a calculated field that would consolidate your $A, $B, $C fields and using it on your Marks shelf instead of all three. I attached an example using your table in the original post. I renamed $A, $B, $C as Field1, Field2, Field3 and then created the following calculated field.

               

              Calculated Field: Field Consolidation

              if not ISNULL([Field1]) then [Field1]

              ELSEIF not ISNULL([Field2]) then [Field2]

              ELSEIF not ISNULL([Field3]) then [Field3]

              ELSE 0

              end

               

              Hopefully this helps, otherwise I'll wait for the superstore example. You could also create a few rows of test data similar to your production data in excel and package the workbook. If you're joining two tables, make sure to use two sheets in excel so it can be replicated properly.

               

              -Wes