Question

1:n with FME, using external Excel file?

  • 10 February 2014
  • 8 replies
  • 2 views
R
Badge
Hello all,

 

 

I've got the following table:

 

Building_ID, Color, Size, Roof

 

1,Red,1-10m,Flat

 

1,Blue,30-40m,Tiles

 

1,Green,20-30m,Flat

 

1,Red,20-30m,Tiles

 

 

Now, I also have this related table which I want to populate:

 

SubBuilding_ID,Item_ID,Subitem_Work,Subitem_Amount,Subitem_Units,Subitem_CPU

 

 

I want to populate this using the following external file:

 

Work_ID,Color,Size,Work,Amount,Units,CPU

 

001,null,1-10m,Paint,10,Liters,5

 

002,null,10-20m,Paint,10,Liters,5

 

003,null,20-30m,Paint,30,Liters,5

 

004,null,30-40m,Paint,40,Liters,5

 

011,Red,null,Flat,Flat Roof,100,m2,10

 

012,Red,null,Tiles,Flat Roof,1000,pieces,2

 

011,Green,null,Flat,Flat Roof,100,m2,8

 

012,Green,null,Tiles,Flat Roof,1000,pieces,1.50

 

011,Blue,null,Flat,Flat Roof,100,m2,6

 

012,Blue,null,Tiles,Flat Roof,1000,pieces,1

 

 

Now, I can get multiple Work types per Building, so I've got a 1:n relationship. The Excel layout is still up for debate, so it can be changed.

 

 

I just can't find out how I would build rows using the external Excel. FeatureMerger is not an option, since it's 1:1... 

 

 

How would I go about building actual rows in this issue?

 

 

Thanks in advance.

8 replies

fmelizard
Rank
Userlevel 4
Badge +13
Hi,

 

 

If you specify a list name and set process duplicate suppliers to yes, the FeatureMerger will relate 1:M. optionally you can use the Joiner.
takashi
Rank
Userlevel 2
Badge +17
Hi,

 

 

The external table looks to consists of 2 tables in fact. That is, the 1st table defines attributes which should be specified based on "Size"; the 2nd table defines attributes which should be specified based on "Color" and "Roof".

 

How about dividing the table into 2 tables? I think the Tester can be used to divide the table by determining if the second column is null.

 

You can then merge each table to the building features with FeatureMerger separately.

 

 

Takashi
R
Badge
Hey Itay, that sounds good! Just thinking how I'd populate the list name using the externa Excel...

 

 

Takashi, the thing is that the provided tables are an example, the "test attributes" can go up to 20 attributes to "map" the values.
R
Badge
Worked out the tables a bit more, and also, for info how the external table should be filled:

 

Table Building

 

Building_ID, Color, Size, Roof

 

1,Red,1-10m,Flat

 

2,Blue,30-40m,Tiles

 

3,Green,20-30m,Flat

 

4,Red,20-30m,Tiles

 

 

External Excel

 

Work_ID,Color,Size,Work,Amount,Units,CPU

 

001,null,1-10m,Paint,10,Liters,5

 

002,null,10-20m,Paint,10,Liters,5

 

003,null,20-30m,Paint,30,Liters,5

 

004,null,30-40m,Paint,40,Liters,5

 

011,Red,null,Flat,Flat Roof,100,m2,10

 

012,Red,null,Tiles, Tiled Roof,1000,pieces,2

 

011,Green,null,Flat,Flat Roof,100,m2,8

 

012,Green,null,Tiles, Tiled Roof,1000,pieces,1.50

 

011,Blue,null,Flat,Flat Roof,100,m2,6

 

012,Blue,null,Tiles, Tiled Roof,1000,pieces,1

 

 

Subtable Work (parent Building)

 

SubBuilding_ID,Building_ID,Subitem_Work,Subitem_Amount,Subitem_Units,Subitem_CPU

 

1,1,Paint,10,Liters,5

 

2,1,Flat Roof,100,m2,10

 

3,2,Paint,30,Liters,5

 

4,2,Tiled Roof,1000,pieces,1

 

5,3,Paint,40,Liters,5

 

6,3,Flat Roof,100,m2,8

 

7,4,Paint,30,Liters,5

 

8,4,Tiled Roof,1000,pieces,2
takashi
Rank
Userlevel 2
Badge +17
I would use 2 FetureMerger in this context.

 

1) Divide the flow of building features into two streams.

 

2) Merge Work_ID 001-004 to 1st stream (FeatureMerger 1: Join On "Size").

 

3) Merge Work_ID 011-012 to 2nd stream (FeatureMerger 2: Join On "Color" and "Roof").

 

4) Send all the Merged features to same Sorter; sort features by Building_ID and Work_ID. Then append SubBuilding_ID with Counter.
R
Badge
Well the thing is still that the Excel could be using ~20 columns to merge on. The Excel is still free to change, to whatever would work better. 

 

 

The biggest problem lies in making multiple objects from 1. What Itay said would be awesome, but could I use the Excel as some sort of "tester" and populate a list with it?
fmelizard
Rank
Userlevel 4
Badge +13
if you merge the features correctly, you can then explode them into multiple features ( "making multiple objects from 1")
takashi
Rank
Userlevel 2
Badge +17
If two or more work types use the same join key, a FeatureMerger can merge them to buildings with "Process Duplicate Suppliers" option, as Itay mentioned.

 

But different key needs different FeatureMerger. In the example you posted, there are two work types, and they use different keys ("Size" and "Color + Roof"), so I think two FeatureMergers will be necessary.

 

 

If a FeatureMerger merged multiple work types (i.e. "Process Duplicate Suppliers" option), the ListExploder can be used to create N rows from 1.

Reply


Community Stats

28,709
Posts
109,481
Replies
36,554
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.