Skip to main content
Solved

Transforming csv data

A

Hi folks,

New to the forum and FME, I'm hoping I can leach some knowledge.

 

I have a csv file that imports into FME like this:

 

I would like to manipulate it into the following format:

 

Depending on which file is imported Costs 1, 2 & 3 may be there, or it may be a combination of 1 or 2 costs. All costs do share a common three characters at the left of their name. Is it possible to dynamically look at the cost columns and then transform the data into the format shown in the second pciture?

 

Thanks in advance.

 

Andrew

Best answer by takashi

Hi @andrew.t, a possible way I can think of is: Branch the workflow into three streams for Cost1, 2, 3, create the new attributes Action and Amount on the streams, connect the streams to a single Tester and keep only the features having 0 < Amount, finally remove unnecessary attributes and re-order the attributes with the AttributeManager if necessary. See also the screenshot below.

Just be aware the value of Action should be a literal string which can be entered with the Text Editor, and the value of Amount should be assigned an attribute value from the original Cost1, 2, 3.

 

 

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

6 replies

takashi
Influencer
  • takashiInfluencer
  • March 4, 2020

Looks like you are attempting to keep only costs greater than zero. If two or three costs in a single record are greater than zero, how do you need to transform the record?

A
  • andrew.t
  • Author
  • March 4, 2020
takashi wrote:

Looks like you are attempting to keep only costs greater than zero. If two or three costs in a single record are greater than zero, how do you need to transform the record?

Hi takashi,

Thanks for the reply.

At present that case does not exist, but if it did I would like those costs to come through as two or three records with the relevant attributes included.

takashi
Influencer
  • takashiInfluencer
  • Best Answer
  • March 4, 2020

Hi @andrew.t, a possible way I can think of is: Branch the workflow into three streams for Cost1, 2, 3, create the new attributes Action and Amount on the streams, connect the streams to a single Tester and keep only the features having 0 < Amount, finally remove unnecessary attributes and re-order the attributes with the AttributeManager if necessary. See also the screenshot below.

Just be aware the value of Action should be a literal string which can be entered with the Text Editor, and the value of Amount should be assigned an attribute value from the original Cost1, 2, 3.

 

 

takashi
Influencer
  • takashiInfluencer
  • March 5, 2020
takashi wrote:

Hi @andrew.t, a possible way I can think of is: Branch the workflow into three streams for Cost1, 2, 3, create the new attributes Action and Amount on the streams, connect the streams to a single Tester and keep only the features having 0 < Amount, finally remove unnecessary attributes and re-order the attributes with the AttributeManager if necessary. See also the screenshot below.

Just be aware the value of Action should be a literal string which can be entered with the Text Editor, and the value of Amount should be assigned an attribute value from the original Cost1, 2, 3.

 

 

Oops, mistake. Please replace all "Amount" in my answer with "Quantity".

A
  • andrew.t
  • Author
  • March 5, 2020
takashi wrote:

Hi @andrew.t, a possible way I can think of is: Branch the workflow into three streams for Cost1, 2, 3, create the new attributes Action and Amount on the streams, connect the streams to a single Tester and keep only the features having 0 < Amount, finally remove unnecessary attributes and re-order the attributes with the AttributeManager if necessary. See also the screenshot below.

Just be aware the value of Action should be a literal string which can be entered with the Text Editor, and the value of Amount should be assigned an attribute value from the original Cost1, 2, 3.

 

 

Thanks very much for the prompt assistance takashi.

takashi
Influencer
  • takashiInfluencer
  • March 5, 2020
takashi wrote:

Hi @andrew.t, a possible way I can think of is: Branch the workflow into three streams for Cost1, 2, 3, create the new attributes Action and Amount on the streams, connect the streams to a single Tester and keep only the features having 0 < Amount, finally remove unnecessary attributes and re-order the attributes with the AttributeManager if necessary. See also the screenshot below.

Just be aware the value of Action should be a literal string which can be entered with the Text Editor, and the value of Amount should be assigned an attribute value from the original Cost1, 2, 3.

 

 

Alternatively the ListPopulator could also help you.

Reply


Helpful Members This Week

Community Stats

31,317
Posts
118,810
Replies
38,946
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.