Skip to main content
  • EN

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

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(featured-1].State and area) = <empty> Then @Value(State and area)
Else @Value(featureÂ-1].State)

0684Q00000ArJnGQAV.png

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.

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(featured-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.

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(featured-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.

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(featured-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 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.

@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


Community Stats

30,117
Posts
114,311
Replies
37,747
Members
Terms & ConditionsCookie 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.