4 Replies Latest reply on Nov 22, 2018 6:15 AM by Terence Hams

    Divide a value by subcomponents

    Terence Hams
    Terence Hams Nov 21, 2018 11:46 AM

      Hi all,

       

      I am new to prep and still learning the program.  I am stumped with how to divide and rename a value based on the subcomponents of that value. In the example picture I am trying to divide and rename the highlighted value under 'Branch' with the three highlighted values under 'Division'. This would result in NRC.RCN being divided into three different values in the 'Branch' field, while retaining all of the other values in 'Branch'.  I can easily do this in Excel but want to perform all of my transformation steps within prep.  Any help would be appreciated.

      prep question.JPG

      • 51 Views
      • Categories: Calculations, Filters, Clean and Shape Data
      • Tags:
        • 1. Re: Divide a value by subcomponents
          Joshua Milligan
          Joshua Milligan Nov 21, 2018 11:59 AM (in response to Terence Hams)

          Hi Terence!

           

          If I'm understanding what you want to do, then I think you can create a calculated field to replace Branch with the correct values.  In fact, if you create a calculated field named Branch it will replace the existing field.  The following code will retain all current values, except in the case of NRC.RCN where values from the Division will be used instead:

           

          IF [Branch] = "NRC.RCN"

          THEN [Division]

          ELSE [Branch]

          END

           

           

          Hope that helps!

          Joshua

          • 2. Re: Divide a value by subcomponents
            Terence Hams
            Terence Hams Nov 21, 2018 12:12 PM (in response to Joshua Milligan)

            Thank you for the quick reply Joshua. You stated my issue correctly.  Unfortunately this did not solve the problem.  The calculation results in the exacts same values in the Branch field.  NRC is not divided and renamed as the subcomponents.

            • 3. Re: Divide a value by subcomponents
              Joshua Milligan
              Joshua Milligan Nov 21, 2018 12:40 PM (in response to Terence Hams)

              That's odd.  A couple of things to try:

              • What happens if you name the calculation something other than Branch?  Any difference?
              • What happens if you double click that branch in the Profile Pane (where it is highlighted in your image above) and then copy the value and paste it into the calculation.  I'm wondering if there are some leading or trailing spaces or another character of some kind that might not result in equality in the calculation I provided.
              • Double check that possibility with a calculation that is simply: [Branch] = "NRC.RCN"
                • If there are no TRUE values, then something is up with the string in the calculation.

               

              Best Regards,

              Joshua

              • 4. Re: Divide a value by subcomponents
                Terence Hams
                Terence Hams Nov 22, 2018 6:15 AM (in response to Joshua Milligan)

                Hi Joshua,

                 

                The calculation worked well.  I copied the text from the branch field and pasted it into the calculation and it worked perfectly.  Not sure what the difference was but I will remember that for the future.  I am glad the calculation is not too complicated.  I really need to find a course or training video just focused on calculations.

                 

                Thanks again,

                Terry