Skip to main content
Question

Creating New Column With Previous Columns Data and Join via Excel

  • December 20, 2018
  • 2 replies
  • 74 views
F
Forum|alt.badge.img

Hi all/season greetings,

 

I am trying to figure if this is best to use SQL or FME.

I have two excel files which look like this below. I want to join the tables together and then produce a extra column based on the changes column. The join bit looks fine.

The next step is the Extra column should be created based on if the changes column either contains a value or not. If it does then it spits a output and if it does not it also does. Only issue i am unable to how to get logic so when you join them based on the record number, you then base the 'extra column' on the changes column being populated with a value.

Description of extra column

Extra Column Title = Status - will be the name of the column.

It then should be able work out based on record number if the changes column has a value in it. If it is empty then the output to that column should be 'not completed' and if it does have a comment then it should come up as 'completed'.

 

So far my flow is:

Excel --> FeatureMerger---> AttributeMapper --> AttributeManager ---> Output Excel

I am just struggling to find the transformer to fill this request.

 

Table 1 (Changes column has been populated in 2/3 rows). So based on this i am after a new column = status and comment added = completed. If it is blank then it should = not completed.

 

 

Record_NumberModifiedTimeChanges5User 109:0110User 208:01Comment Added15User 307:01Comment Added

 

Table 2 (All Changes are empty in this column)

 

 

Record_NumberModifiedTimeChanges5User 110User 2

 

15User 32012

2 replies

redgeographics
Celebrity
Forum|alt.badge.img+50

If you put Table1 in the FeatureMerger as the Requestor and then set the Accumulation Mode to Merge Supplier and the Conflict Resolution to Use Requestor the resulting set will have a value for Changes for those records where that attribute is filled in Table1.

Next you can use an AttributeManager to add a column, Status, and fill it with a conditional value.

Out of curiousity: why are you joining the tables? In your example Table2 doesn't seem to be adding any new information.

F
Forum|alt.badge.img
  • fazsnatch
  • Author
  • December 20, 2018
redgeographics wrote:

If you put Table1 in the FeatureMerger as the Requestor and then set the Accumulation Mode to Merge Supplier and the Conflict Resolution to Use Requestor the resulting set will have a value for Changes for those records where that attribute is filled in Table1.

Next you can use an AttributeManager to add a column, Status, and fill it with a conditional value.

Out of curiousity: why are you joining the tables? In your example Table2 doesn't seem to be adding any new information.

Thank you, this is perfect. I am complete newbie. I have managed to do many requests but in terms of digging into way clauses and parameters work, this is probably next step. Thanks for sharing this, it is huge help.

Reply


Helpful Members This Week

Community Stats

31,370
Posts
118,973
Replies
39,000
Members

Latest FME

Terms & ConditionsCookie settings

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
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.