Question

Convert each list element to multiple new attributes

  • 20 July 2017
  • 4 replies
  • 46 views
T
Badge

I am trying to create a new attribute for each element in a list. The tables below show the desired outcome. Here is an explanation.

For every item in the MASTER list I am loading its corresponding detail list and saving it as a parameter. I want to take the corresponding list and transform it to the example below. I could accomplish this with a database view or query, but would like to be able to do it in FME.

I can assume that all ITEM_NAME values are known, which means all the possible attributes that I need to add could be added ahead of time if needed. Note that for every record in the master list, it won't have all of the possible ITEM_NAME values. My process right now is very brute force, and I am thinking there may be a transformer I haven't found that does this or a variation of this easily.

 

MASTER List

IDNAME1Bill2Sally

 

DETAIL List

MASTER_IDDETAIL_IDITEM_NAMETYPECOLOR1AShirtLongRed1BPantsJeansBlue2CShirtShortGreen2DPantsDressPurple2EHatBaseballRed

Transformed Master List with Detail Denormalized

IDNAMESHIRT_TYPESHIRT_COLORPANTS_TYPEPANTS_COLORHAT_TYPEHAT_COLOR1BillyLongRedJeansBluenullnull2SallyShortGreenDressPurpleBaseballRed

 

Thanks, Tom

4 replies

erik_jan
Rank
Userlevel 2
Badge +12

The first step would be to join the master list items to all detail list items using the FeatureMerger (create a list for multiple details).

Assuming the details are always ordered you could the create the attributes with AttributeCreator (or AttributeManager) using the "Conditional Values" option (e.g. SHIRT_TYPE = if _list.ITEM_NAME = "Shirt" Then TYPE Else Null).

Hope this gets you in the right direction.

takashi
Rank
Userlevel 2
Badge +17

Hi @t2, I think this workflow does the trick.

  1. BulkAttributeRenamer: Add a prefix (upper case item name and an underscore) to the attribute names "TYPE" and "COLOR".
  2. FeatureMerger: Merge DETAIL List to MASTER List joining on ID of persons. Important: Set 'Yes' to the Process Duplicate Suppliers parameter.
  3. AttributeExposer: Expose the prefixed attribute names.
  4. AttributeManager: Remove unnecessary attributes.

0684Q00000ArKDcQAN.png

0684Q00000ArKDhQAN.png

Alternatively, the InlineQuerier works as well.

0684Q00000ArJNAQA3.png

InlineQuerier | SQL Query:

select
    a.ID,
    a.NAME,
    b.TYPE  as SHIRT_TYPE,
    b.COLOR as SHIRT_COLOR,
    c.TYPE  as PANTS_TYPE,
    c.COLOR as PANTS_COLOR,
    d.TYPE  as HAT_TYPE,
    d.COLOR as HAT_COLOR
from "MASTER List" as a
left outer join (select * from "DETAIL List" where ITEM_NAME = 'Shirt') as b
    on b.MASTER_ID = a.ID
left outer join (select * from "DETAIL List" where ITEM_NAME = 'Pants') as c
    on c.MASTER_ID = a.ID
left outer join (select * from "DETAIL List" where ITEM_NAME = 'Hat') as d
    on d.MASTER_ID = a.ID

Hope this helps.

T
Badge

@takashi, Thanks, I appreciate both options. The second option you gave was inline with my fallback to sql, but I wanted to try it more along the lines of what you did in the first option, so thank you for both!

Tom

smfks911

Hi @Takashi Iijima​, I am following this thread and your solutions are really very effective. Slightly like similar problem, I would like to create two attributes by splitting a 'Road_name' attribute. So when road name is like Harris Rd, I will split them into two attributes 'type' and 'name' by recalling _list{1} and _list{0}. but when the Road_name has three words like David Conway St, I cannot simply do it by recalling _list{0,1}. What else I can do for such situation?

Reply


Community Stats

28,670
Posts
109,364
Replies
36,531
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.