I have an FME script which takes specific headers of a source spreadsheet and copies the them and the field values to two new fields (Substrate Size Distribution & Substrate Size Distribution Value). There are transformers which replace alphanumeric characters from the original header string, sorts the numbers that are left, and concatenates the alphanumeric characters back to the sorted values. This works great. But, the script is coping a header from a field called "Visit_Year" to Substrate Size Distribution field and its value, "1989", to Substrate Size Distribution Value, where values are null in the specific headers I am trying to reformat. This is not what I want. See attachment. I want Substrate Size Distribution and Substrate Size Distribution Value fields to contain only the headers and their values, not the header text Visit_Year. Thoughts as to what is going on?
Unfortunately it is very difficult to tell why since we do not know how the data is processed. Is it possible to post the workspace with some sample input data?
@david_r Hi David. I could really use some help. I have modified my ETL since I first posted the question. I am attaching it and the original and resultant spreadsheets. As before, I have two fields which were created in the script to store field names representing Substrate Weight Captured and Substrate Weight Value based on a list of fields from the original spreadsheet. The script does everything I need it to do except for one thing. It is posting a value from the Distance Left Bank Pin field into the Substrate Weight Captured field. This is incorrect. When the Distance Left Bank Pin field is empty or null, this behavior does not occur. There should only be values in this field for D15 mm, D35 mm, D50 mm, D84 mm, D95 mm, and D100 mm from the original spreadsheet. In the spreadsheet: Step2_SubstrateWeightCaptured, I highlight the fields in yellow which belong in the first subset of data and the one that doesn't in gray. I am trying to resolve the first record that is highlighted in gray. Your thoughts? Joe
Hi Joe, it looks like there's a mismatch between the input feature type definition and the actual Excel input file. You'll see it if you send the input data directly to an Inspector; the column values there will all show <missing> rather than the actual values.
The easiest fix is probably to re-create the Excel reader.
Hi Joe, it looks like there's a mismatch between the input feature type definition and the actual Excel input file. You'll see it if you send the input data directly to an Inspector; the column values there will all show <missing> rather than the actual values.
The easiest fix is probably to re-create the Excel reader.
Hi David, I definitely looked at the input and saw the missing values in the inspector. These are cells in the spreadsheet that had no data. I did recreate the reader to account for change in the data type for a few fields that I noted were incorrect. I used multiple inspectors around the front end of the ETL to determine what was going on. I eve used the NullAttributeMapper to turn all empty or <missing> fields to Null. I am still having the problem mentioned above. But, I think it is occuring between the Tester transformer and the AttributeCreator. The AttributeCreator is storing the list of field names and values in an array. I believe when it transposing the data from columns to rows under the Substrate Weight Captured and Substrate Weight Value fields (newly created in the AttributeCreator) that it is picking up those null values and placing data in their place using values from the Distance Bank Left Pin field. Joe
Hi @jencinas , if I have understood your requirement correctly, the attached workspace example would illustrate a possible solution for your major issue.
Hi @jencinas , if I have understood your requirement correctly, the attached workspace example would illustrate a possible solution for your major issue.
Hello @Takashi Iijima , Unfortunately, I have FME 2018 and when I open the example you provided, FME warns me there are some transformers that aren't installed on my corporate system (i.e. attributesplitter (version 4)). Can you provide this example in the 2018 format? I do appreciate it. I wasn't able to see the complete ETL you provided. You definitely offer a different approach which I appreciate. It looks like your script handles the data more efficiently.
Hello @Takashi Iijima , Unfortunately, I have FME 2018 and when I open the example you provided, FME warns me there are some transformers that aren't installed on my corporate system (i.e. attributesplitter (version 4)). Can you provide this example in the 2018 format? I do appreciate it. I wasn't able to see the complete ETL you provided. You definitely offer a different approach which I appreciate. It looks like your script handles the data more efficiently.
@Takashi Iijima , I got it to work! I added the AttributeSplitter to the ETL you provided. This was missing when I opened your copy. Probably because of the difference in versions. I was able to insert this transformer between the StringConcatenator and AttributeManager. This result is exactly what I wanted! Thank you so much for showing me an alternative method for creating this product! I have what I needed. I appreciate the efficiency of your script and will incorporate it into my workflow. Joe