Skip to main content
Question

Two Lookups

  • August 31, 2018
  • 3 replies
  • 26 views
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? :) ]

    This post is closed to further activity.
    It may be an old question, an answered question, an implemented idea, or a notification-only post.
    Please check post dates before relying on any information in a question or answer.
    For follow-up or related questions, 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.

    3 replies

    ebygomm
    Influencer
    Forum|alt.badge.img+44
    • ebygommInfluencer
    • Influencer
    • 3422 replies
    • 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
    Safer
    Forum|alt.badge.img+20
    • fmelizardSafer
    • Safer
    • 3719 replies
    • 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
    • 6 replies
    • September 2, 2018
    Thank-you so much @egomm and @daleatsafe - I will try both techniques for my education.

    Community Stats

    32,060
    Posts
    121,724
    Replies
    39,688
    Members

    Latest FME

    Terms & ConditionsCookie settingsAccessibility statement
    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.