Question

Two Lookups

  • 31 August 2018
  • 3 replies
  • 10 views
S
Badge

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
Rank
Userlevel 1
Badge +21

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
Rank
Userlevel 4
Badge +13

@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
Badge
Thank-you so much @egomm and @daleatsafe - I will try both techniques for my education.

Reply


Community Stats

28,731
Posts
109,530
Replies
36,565
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.