Skip to main content
Solved

How to merge two data sets with connecting IDs, when some of the attributes need to be transposed?

  • March 30, 2018
  • 2 replies
  • 193 views
L

Hi there,

I'm looking for a way how to bring together these two data-sets.

 

 

1st data-set contains records with unique ID and several attributes: Connect_ID Facility TypeName 3 Cafe Ice-creamAAA 12 Store GroceryBBB 25 Store ElectronicsCCC

 

2nd data-set contains IDs, days and operational hours:

Connect_IDDay of the weekOpensCloses3Monday8:00:0018:00:00 3Tuesday8:00:0018:00:00 3Wednesday8:00:0018:00:00 3Thursday8:00:0018:00:00 3Friday8:00:0018:00:00 3Saturday9:00:0014:00:00 3Sunday12Monday8:00:0017:00:00 12Tuesday8:00:0017:00:00 12Wednesday8:00:0017:00:00 12Thursday8:00:0017:00:00 12Friday8:00:0017:00:00 12Saturday9:00:0015:00:00 12Sunday25Monday8:00:0018:00:00 25Tuesday8:00:0018:00:00 25Wednesday8:00:0018:00:00 25Thursday8:00:0018:00:00 25Friday8:00:0018:00:00 25Saturday9:00:0018:00:00 25Sunday

 

What I would like to do is bring these tables together, but they should have 1 row per ID, so the days and the opening hours should be transposed to new columns, like this:

Connect_IDFacilityTypeNameMonday_opensMonday_closesTuesday_opensTuesday_closesWednesday_opensWednesday_closes3CafeIce-creamAAA8:00:0018:00:008:00:0018:00:008:00:0018:00:0012StoreGroceryBBB8:00:00

17:00:00 8:00:0017:00:00 8:00:0017:00:00 25StoreElectronicsCCC8:00:0018:00:008:00:0018:00:008:00:0018:00:00

What I tried so far was to use a Feature merger, having the 1st set as requestor and opening hours data set as supplier, but this way I can't get all the days of the week.

 

(FME Desktop 2018)

 

 

Any help will be much appreciated!

 

 

Thanks,

 

Linda

Best answer by takashi

Hi @llindish, this workflow might help you. Add week day prefix to the attribute names "Opens" and "Closes" for each row in the table 2, and merge table 2 to table 1. Then, if necessary, expose renamed attributes and remove unnecessary attributes.

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.

2 replies

takashi
Celebrity
  • takashiCelebrity
  • 7843 replies
  • Best Answer
  • March 30, 2018

Hi @llindish, this workflow might help you. Add week day prefix to the attribute names "Opens" and "Closes" for each row in the table 2, and merge table 2 to table 1. Then, if necessary, expose renamed attributes and remove unnecessary attributes.

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
L
  • llindish
  • Author
  • 10 replies
  • April 3, 2018

Hi @llindish, this workflow might help you. Add week day prefix to the attribute names "Opens" and "Closes" for each row in the table 2, and merge table 2 to table 1. Then, if necessary, expose renamed attributes and remove unnecessary attributes.

Thanks @takashi, works perfectly!

 

At first I thought I was having some issues with attributeExposer as I couldn't see my newly created attributes in the drop-down list, but turns out that you have to type them in manually.

 

Community Stats

32,087
Posts
121,853
Replies
39,729
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.