Skip to main content

Hi all. I’m trying to generate UIDs for helping out with site inspection reports for structures. The structures are comprised of “Components” and each “Component” is comprised of “Sub-Components”. Each “Sub-Component” has a inspection lists (“Lists”) that are checkboxes that need to occur for each “Sub-Component”. Each checkbox is a coded item (“Code”) that must be inspected. There are multiple “Codes” for each of the “Lists”.

The UID format that is desired is this:

“Site ID”. “Component ID” . “Sub-Component ID” . “Code”

The problem I am running into is that the data have been provided in Excel. There is a “SiteVisits” Tab, and a “Lists” tab. The SiteVisits tab has 350+ records, with columns containing Site IDs, Component IDs and Sub-Component IDs, and a column on the “Lists” required for each Site.

SiteVisits Tab, with 350+ records

The “Lists” Tab contains a descriptor of each checkbox and the associated “Code”. What I am trying to do is concatenate the easy parts of the UID (“Site ID”. “Component ID” . “Sub-Component ID”), with each of the “Code” values on the “Lists” tab.

Lists Tab, with relevant codes for each list. Note that the List # row is two columns merged together

 

I am trying to tell FME to explode the column that outlines all the “Lists” on the SiteVisits tab, and then Join all of the relevant “Codes” for whichever “List” is required for that site, and then generate the codes with an AttributeManager. I’d then end up with a whole bunch of UIDs, examples are shown below:

Examples of desired UIDs

 

There are around 350 Sites. Each site has multiple lists, and each list has multiple codes, and there are a total of 73 lists, so I’m looking at an output of around 20000+ UIDs. I have attached a trimmed copy of the data provided. This contains the “SiteVisits” and “Lists” tabs mentioned above, with examples of the desired UID outputs in the “SiteVisits” tab.

If anyone has any ideas, it would be greatly appreciated :)

Explode the site table on List (ColumnH) then combine all the List tables into one with List1,CodeA
List1,CodeB
List1,CodeC
List…,Code….

Then Join the two tables on columns containing the List#
Hope that helps. Good Luck!


Explode the site table on List (ColumnH) then combine all the List tables into one with List1,CodeA
List1,CodeB
List1,CodeC
List…,Code….

Then Join the two tables on columns containing the List#
Hope that helps. Good Luck!

Hi there, thanks for this! How do I combine all of the list tables into one column? The columns with the codes are the List # + “00” since the List # is a merged cell in Excel:

Thanks again!
 


AttributeExploder will help you here. Read in the sheet with the lists in columns then use an attributekeeper to drop the description columns then AttributeExploder. There may be some clean up after that but this will get your table into 2 columns one with List# and one with ListValue. Hope this helps!


AttributeExploder will help you here. Read in the sheet with the lists in columns then use an attributekeeper to drop the description columns then AttributeExploder. There may be some clean up after that but this will get your table into 2 columns one with List# and one with ListValue. Hope this helps!

Thanks a lot for this! I am pretty much there, the last component is to rejoin the Descriptions back to the Codes to then populate another field with the description. So my schema is loosely:

Site ID | Component ID | Sub-Component ID | Code | Description

I have managed to generate values for each field within FME apart from the Description. I had to manually copy and paste the Descriptions and Codes into a separate Excel sheet into a column for both, and then join this Excel sheet to the Code field to then populate the Description. Do you have any flash ideas for how I could replicate me manually copy and pasting all of the Descriptions and Codes, so that the end result is a column of all the Descriptions and a column of all the Codes?

Thanks again for your time!


Heyo! Glad that got you closer. But Oooo Yeah. Booo on the Descriptions

You could do two AttributeExploders and use the Ignore Attributes Containing option and set it to ‘00’ . If the read remains consistent and your Descriptions are in col ‘List (n)’ and the Codes are in col ‘List n00’ then you should be good to go. You should be able to stitch everything back together using the xlsx_row_id. Hopefully that get you over the line!