Skip to main content
Question

Concatenate to solve for duplicate values

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

1 Attachments

7 replies

takashi
Influencer
  • takashiInfluencer
  • 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

erik_jan
Contributor
Forum|alt.badge.img+18
  • 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
takashi wrote:

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
takashi wrote:

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
takashi wrote:

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
Influencer
  • takashiInfluencer
  • May 17, 2016
dshaw wrote:

@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.

D
Forum|alt.badge.img
  • dshaw
  • Author
  • May 17, 2016
takashi wrote:

@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.

Reply


Helpful Members This Week

Community Stats

31,330
Posts
118,863
Replies
38,957
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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.