Skip to main content
Solved

Excel sheet flattening for UID Generation

  • February 20, 2025
  • 5 replies
  • 62 views

jeetz93
Contributor
Forum|alt.badge.img+2

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 :)

Best answer by crutledge

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!

View original
Did this help you find an answer to your question?
This post is closed to further activity.
It may be a question with a best answer, an implemented idea, or just a post needing no comment.
If you have a follow-up or related question, 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.

5 replies

crutledge
Enthusiast
Forum|alt.badge.img+30
  • Enthusiast
  • February 20, 2025

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!


jeetz93
Contributor
Forum|alt.badge.img+2
  • Author
  • Contributor
  • February 24, 2025
crutledge wrote:

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!
 


crutledge
Enthusiast
Forum|alt.badge.img+30
  • Enthusiast
  • Best Answer
  • February 25, 2025

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!


jeetz93
Contributor
Forum|alt.badge.img+2
  • Author
  • Contributor
  • March 4, 2025
crutledge wrote:

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!


crutledge
Enthusiast
Forum|alt.badge.img+30
  • Enthusiast
  • March 4, 2025

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!
 

 


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