1

Is there a "CASE" type transformer in FME? If so, what is it called? Failing that, a transformer with a wide if, else if, else if, else if... else?

I'm currently working with an organisation that's in the middle of a large database shift project, total schema change, etc. I have been working on "business as usual" making sure new data is still being added to the database while not being involved directly with the project. Part of the "business as usual" means I have tools that capture new data (spatial and a lot of attribution) into our old schema and then some FME workbench files that convert that new data into the new schema before being loaded into the new database. This all actually works really well.

Last week, however, I was asked to concatenate additional info into a new text field. This would all be based on a large if, else if, else type model, or maybe a case tool would be simpler.

eg

if      field1 = x and field2 = y and field3 = z then field4 = 1
else if field1 = x and field2 = y and field3 = w then field4 = 2
else if field1 = x and field2 = g and field3 = t then field4 = 3
...
else field4 = 10

I know my example is a bit of a mix of different flavours, but just trying to simplify what I'm trying to do.

Currently I'm working with the AttributeManager transformer and the Conditional Value option against the field I'm concatenating into, and I think it's the closest I've found to doing what I want, but it's quite messy. Am I missing a better transformer that my searches haven't found? The best option for me (I think) would be something that would read an XLSX file (similar to SchemaMapper) and step through the different columns to compare field values and then output the desired final value.

1 Answer 1

1

That's an interesting question. I think that there are multiple ways of handling this scenario.

The first is to use Conditional Values, as you currently are.

It's really what that tool was designed for; it's just a bit of a pain sometimes, to dive deep down into it. My advice is to make sure that you use the Copy/Paste tools in there because they can really help. You can copy individual rows from one attribute and paste them into another, or even paste them into another transformer altogether. It can be a huge timesaver.

The second solution is to use the TestFilter transformer.

That transformer is the closest thing in FME to a CASE statement. The first line is your IF statement and the rest of the lines are ELSEIF or ELSE. But, as the name suggests, it's only filtering the data, rather than setting a value. So you would need to use an AttributeCreator (or similar) attached to each output port. It is way easier to use than Conditional Values, though, so would be useful if you are creating workspaces for less-experienced users to maintain.

Oh, the TestFilter also gives you the option to apply all of the rows. Sort of an IF... THEN... AND, (instead of IF... THEN... ELSE). i.e. normally a feature is filtered out as soon as it hits a match, but you can make it continue to check every row for a match. Not sure if it will help here, but I thought I'd mention it.

If you want to use a lookup file, in Excel, for example, then there are a few ways of doing that too. I'd use a join or merge transformer. For example, read the Excel data into the workspace and use a FeatureMerger where the original data is the Requestor and the Excel data is the Supplier. Or just use a DatabaseJoiner to read directly from the Excel sheet.

Either way, you'd set up a spreadsheet like:

field1,field2,field3,field4
x,y,z,1
x,y,w,2
x,g,t,3
etc

Then set up your Join fields like so:

enter image description here

The obvious disadvantage compared to the TestFilter/Conditional Values, is that you can't use any test except equivalency. e.g. you can't say if field 1 > 3 because merging doesn't allow greater thans.

The SchemaMapper transformer would also be a good way to do this, although I don't see it being hugely different to a joiner/merger transformer.

Instead of using the Join On fields, you would use the Filter options. To set the value you would use a New Attribute option. Your Excel table would look a little different:

field1,field2,field3,newfield,newfieldvalue
x,y,z,field4,1
x,y,w,field4,2
x,g,t,field4,3
etc

There are multiple SchemaMapper examples on the FME Community - mostly under this tutorial. Just be aware that about 75% of that content won't apply to you, because you aren't mapping table names or field names, just setting values.

I hope this helps. I can't see any other ways of doing this, at least nothing that would be more efficient. I mean, the AttributeValueMapper would be good, but only if you have a single match (if Field1==x then Field4=2) and the AttributeRangeMapper if you have a single match but in a range of numbers (if Field1 >0 and Field 1 <10 then Field 4=2).

1
  • 1
    "would be useful if you are creating workspaces for less-experienced users to maintain" - this has been at the back of my mind as ultimately this will be maintained by less-experienced, or even non FME experienced, users. Which is why I was hoping for an XLSX solution (and will be testing your Excel lookup file suggestion). That said the TestFilter sounds promising. Will let you know how I get on
    – Midavalo
    Commented Jun 6, 2023 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.