Skip to main content
  • EN

Hello.

I am needing to lookup attribute values from my source table and lookup the attribute name and value in an excel file to return a the new mapped value. If the source attribute name and value matches the lookup list, return the new value.

I have tried different combinations of mergers, exploders, joiners. Its a massive dataset with dozens of attributes to lookup per record. Any guidance would be appreciated. The challenge mostly that source attributes to align are values in the lookup

For example:

Source Data Example

STATUSMANUFACTURERMATERIALCREATEDBY0

ABC_CO

John0ZYX_INC1John

0

ZYX_INCJohn

Lookup XLS


IDDOMAIN1ORIGVALUENEWVALUE1MANUFACTURERABC_COABCCO2MANUFACTURERZYX_INCXYZINC3MATERIAL UNKNOWN4MATERIAL1PLASTIC5STATUS0ACTIVE

Desired Target


STATUSMANUFACTURERMATERIALCREATEDBYACTIVEABCCOUNKNOWNJohnACTIVEZYXINCPLASTICJohnACTIVEZYXINCUNKNOWNJohn

 

Is there overlap in the values for the different columns? I.e. can STATUS be 1 too?

Is there overlap in the values for the different columns? I.e. can STATUS be 1 too?

Yes there will be overlaps accross domains

 

 

Yes there will be overlaps accross domains

 

 

That complicates matters. It does look like something the SchemaMapper should be able to handle, there's a tutorial that will help you get started with it.

 

 

Hi @mhollas, a bit long story. Please find the attached workspace example.

attribute-lookup.fmwt (FME 2018.0.1.0)

Hi @mhollas, a bit long story. Please find the attached workspace example.

attribute-lookup.fmwt (FME 2018.0.1.0)

Brilliant! My initial tests pass. Ill continue with this and report back. Thank you @takashi

 

 

Hi @mhollas, a bit long story. Please find the attached workspace example.

attribute-lookup.fmwt (FME 2018.0.1.0)

0684Q00000ArJSGQA3.png

Good to hear. If you are familiar with (or interested in) Python scripting, see also this one.

 

# PythonCaller script example
class FeatureProcessor(object):
    def __init__(self):
        self.lookUp = {}
        
    def input(self, feature):
        if feature.getAttribute('_is_lookup'):
            d = feature.getAttribute('DOMAIN1')
            o = feature.getAttribute('ORIGVALUE')
            n = feature.getAttribute('NEWVALUE')
            self.lookUp.setdefault(d, {})bo] = n
            
        else:
            for attr in  a for a in feature.getAllAttributeNames() if a in self.lookUp]:
                v = feature.getAttribute(attr)
                feature.setAttribute(attr, self.lookUpiattr].get(v, '<undefined>'))
            self.pyoutput(feature)
            
    def close(self):
        pass

0684Q00000ArM6PQAV.png

Reply


Community Stats

30,159
Posts
114,480
Replies
37,807
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.