Skip to main content
Question

Concatenate to solve for duplicate values

  • May 16, 2016
  • 7 replies
  • 77 views
D
Forum|alt.badge.img

I have an issue where I want to concatenate data with header from the row. I have BLS file that has CBSA/MSA data by state. I need to concatenate the CBSAs of each state with the state name which begins in the row. Each state is separated by space. So, for example Alabama, then the list of CBSA/MSAs, then a space, followed by the next state, followed by CBSAs, etc. So, I want to be able to read the file and create a new column with the CBSA Name and State Name so it creates a unique identifier. I need this unfortunately because some states have CBSA/MSAs with the same name. The file is what I would say is semi-unstructured. @takashi

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

7 replies

takashi
Celebrity
  • takashiCelebrity
  • May 16, 2016

Hi @dshaw, I would add a new attribute called e.g. "State", using the AttributeCreator with the "Enable Adjacent Feature Attributes" option.

0684Q00000ArJg8QAF.png

  1. Excel Reader: Use the 5th row as field names, and read data rows from the 6th row.

  2. AttributeExposer: Expose "State".
  3. AttributeCreator: Check the "Enable Adjacent Feature Attributes" option and set 1 to the "Number of Prior Features". Then, create the value of "State" as a conditional value like this.
If @Value(feature[-1].State and area) = <empty> Then @Value(State and area)
Else @Value(feature[-1].State)

0684Q00000ArJnGQAV.png

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
erik_jan
Contributor
Forum|alt.badge.img+26
  • erik_janContributor
  • Contributor
  • May 16, 2016

Another option is using the VariableSetter and VariableRetriever:

If the attribute contains a state (not starting with a white space) set a variable called State. If the attribute is not a state, retrieve the variable State and concatenate to the area name.

D
Forum|alt.badge.img
  • dshaw
  • Author
  • May 16, 2016

Hi @dshaw, I would add a new attribute called e.g. "State", using the AttributeCreator with the "Enable Adjacent Feature Attributes" option.

0684Q00000ArJg8QAF.png

  1. Excel Reader: Use the 5th row as field names, and read data rows from the 6th row.

  2. AttributeExposer: Expose "State".
  3. AttributeCreator: Check the "Enable Adjacent Feature Attributes" option and set 1 to the "Number of Prior Features". Then, create the value of "State" as a conditional value like this.
If @Value(feature[-1].State and area) = <empty> Then @Value(State and area)
Else @Value(feature[-1].State)

0684Q00000ArJnGQAV.png

Thank so much!  I am still learning this program and every day I get better.  I really appreciate this.  I am going to give it a try now.  This community is awesome.

D
Forum|alt.badge.img
  • dshaw
  • Author
  • May 16, 2016

Hi @dshaw, I would add a new attribute called e.g. "State", using the AttributeCreator with the "Enable Adjacent Feature Attributes" option.

0684Q00000ArJg8QAF.png

  1. Excel Reader: Use the 5th row as field names, and read data rows from the 6th row.

  2. AttributeExposer: Expose "State".
  3. AttributeCreator: Check the "Enable Adjacent Feature Attributes" option and set 1 to the "Number of Prior Features". Then, create the value of "State" as a conditional value like this.
If @Value(feature[-1].State and area) = <empty> Then @Value(State and area)
Else @Value(feature[-1].State)

0684Q00000ArJnGQAV.png

@takashi can you help me with the conditional dialogue box, I have never used that before either and I cannot figure out what I am doing wrong there.

D
Forum|alt.badge.img
  • dshaw
  • Author
  • May 16, 2016

Hi @dshaw, I would add a new attribute called e.g. "State", using the AttributeCreator with the "Enable Adjacent Feature Attributes" option.

0684Q00000ArJg8QAF.png

  1. Excel Reader: Use the 5th row as field names, and read data rows from the 6th row.

  2. AttributeExposer: Expose "State".
  3. AttributeCreator: Check the "Enable Adjacent Feature Attributes" option and set 1 to the "Number of Prior Features". Then, create the value of "State" as a conditional value like this.
If @Value(feature[-1].State and area) = <empty> Then @Value(State and area)
Else @Value(feature[-1].State)

0684Q00000ArJnGQAV.png

@takashi or am I missing something in the AttributeExposer?  I seem to be missing something.

takashi
Celebrity
  • takashiCelebrity
  • May 17, 2016

@takashi can you help me with the conditional dialogue box, I have never used that before either and I cannot figure out what I am doing wrong there.

@dshaw, can these images help you?

1. Click the menu button and select the "Conditional Value" command..

2. Define the value. See also this page: Parameter Condition Definition Dialog.

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
D
Forum|alt.badge.img
  • dshaw
  • Author
  • May 17, 2016

@dshaw, can these images help you?

1. Click the menu button and select the "Conditional Value" command..

2. Define the value. See also this page: Parameter Condition Definition Dialog.

@takashi yes that is what I was doing except for the think the issue was with the ="" but I was able to use the AttributeManager instead of the AttributorExposer and ended up getting it to work. You have been so very helpful. Thank you very much.

Community Stats

32,666
Posts
123,963
Replies
41,099
Members
Terms & ConditionsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.