Skip to main content
Question

Two Lookups

S
Forum|alt.badge.img

I am a beginner FME user having trouble with using a lookup table. I would appreciate your help.

I will need to perform two lookups (there are plenty of examples here using a single lookup).

The primary dataset has a structure like this:

SEQID1ID21FTYCOM2PRDDCY

The lookup table provides a name for each ID:

IDNAMECOMCommodoreDCYDiscoveryFTYFtypePRDPradoCORCorollaFALFalcon

I would like to perform the two lookups for each row of the primary dataset in order to achieve the result:

SEQID1NAME1ID2NAME21FTYFtypeCOMCommodore2PRDPradoDCYDiscovery

The above examples are simplified. Both the primary dataset and the lookup table contain thousands of rows.

Any ideas please? [Did I mention I'm a beginner? :) ]

    3 replies

    ebygomm
    Influencer
    Forum|alt.badge.img+31
    • ebygommInfluencer
    • Influencer
    • August 31, 2018

    I would use an inlinequerier for this 

    0684Q00000ArJyQQAV.png

    Select
  p.SEQ,
  p.ID1,
  l1.NAME As NAME1,
  p.ID2,
  l2.NAME As NAME2
From primary_table p
Join lookup l1 On ( l1.ID = p.ID1 )
Join lookup l2 On ( l2.ID = p.ID2 )

    fmelizard
    Contributor
    Forum|alt.badge.img+17
    • fmelizardContributor
    • Contributor
    • September 1, 2018

    @egomm's technique will certainly work and work well. The slightly easier-to-set-up way is to use a traditional reader to read your pimary dataset, then go into a DatabaseJoiner to do the ID1 lookup, then copy that DatabaseJoiner transformer and connect it at the end and do the ID2 lookup.

    S
    Forum|alt.badge.img
    • sunorchids
    • Author
    • September 2, 2018
    Thank-you so much @egomm and @daleatsafe - I will try both techniques for my education.

    Reply


    Helpful Members This Week

    Community Stats

    30,963
    Posts
    117,356
    Replies
    38,749
    Members

    Latest FME

    Terms & ConditionsCookie settings

    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
    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.