Skip to main content
Solved

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


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.

View original
Did this help you find an answer to your question?

takashi
Contributor
Forum|alt.badge.img+17
  • Contributor
  • 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.


takashi wrote:

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.

 


Reply


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