Skip to main content
Question

Fill values automatically

  • April 27, 2020
  • 14 replies
  • 137 views
mr_fme
Enthusiast
Forum|alt.badge.img+9

hi,

 

I have an excel file, which has 3 fields: date, confirmed and country. But some data and confirmed values are without values. For those who are without values, I would like them to be filled out, considering the last value. In other words, the last data for a given country was 04/29 and the next data record for empty, the filling must be automatic for 04/30. The same thing should happen for "confirmed" if the last record is determined for 50 country and the next record is empty for that same country, the filled value must be 50, always the previous value.

 

thank´s

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.

14 replies

danilo_fme
Celebrity
Forum|alt.badge.img+51
  • danilo_fmeCelebrity
  • Celebrity
  • 2077 replies
  • April 27, 2020

Hello,

Please, could you share your data?

 

Thanks in Advance,

Danilo

Partner Solutial Brazil - www.solutial.com.br
mr_fme
Enthusiast
Forum|alt.badge.img+9
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • 145 replies
  • April 27, 2020

sheet.xlsxFollows my excel file. Thank´s @danilo_fme

bwn
Evangelist
Forum|alt.badge.img+26
  • bwnEvangelist
  • Evangelist
  • 562 replies
  • April 28, 2020

This can be done in an AttributeCreator by:

  • Turning On "Adjacent Feature Handling" Preceding Features = 1
  • Setting the value by Conditional Values. Eg. If NOT @Value(Confirmed) Attribute Has a Value Then Set Attribute Value = @Value(Confirmed[-1]).

This will set the value to the the preceding value (The [-1] is the relative index from the current Feature), where the Value is Missing or NULL.

Similar logic for the "Date" Attribute, except the Attribute is set by adding a Day to the previous Date by referencing Date[-1] in the same AttributeCreator and using the Date/Time FME functions such as @DateTimeAdd().

 

mr_fme
Enthusiast
Forum|alt.badge.img+9
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • 145 replies
  • April 28, 2020

This can be done in an AttributeCreator by:

  • Turning On "Adjacent Feature Handling" Preceding Features = 1
  • Setting the value by Conditional Values. Eg. If NOT @Value(Confirmed) Attribute Has a Value Then Set Attribute Value = @Value(Confirmed[-1]).

This will set the value to the the preceding value (The [-1] is the relative index from the current Feature), where the Value is Missing or NULL.

Similar logic for the "Date" Attribute, except the Attribute is set by adding a Day to the previous Date by referencing Date[-1] in the same AttributeCreator and using the Date/Time FME functions such as @DateTimeAdd().

 

Thank´s @bwn

 

But unfortunately in the final result, the empty fields are using the confirmed values from the previous feature, regardless of the country. This resolution should be grouped by country. Example Canada cannot use the previous feature value if it is USA, the name of the country.
ebygomm
Influencer
Forum|alt.badge.img+44
  • ebygommInfluencer
  • Influencer
  • 3427 replies
  • April 28, 2020

Thank´s @bwn

 

But unfortunately in the final result, the empty fields are using the confirmed values from the previous feature, regardless of the country. This resolution should be grouped by country. Example Canada cannot use the previous feature value if it is USA, the name of the country.

In which case you just need to sort by country then add an additional test to check if the country value is the same as the previous feature

mr_fme
Enthusiast
Forum|alt.badge.img+9
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • 145 replies
  • April 28, 2020

In which case you just need to sort by country then add an additional test to check if the country value is the same as the previous feature

 

could you explain it better? I´m new in FME
ebygomm
Influencer
Forum|alt.badge.img+44
  • ebygommInfluencer
  • Influencer
  • 3427 replies
  • April 28, 2020

 

could you explain it better? I´m new in FME

So a sorter prior to the attribute creator, then the attribute creator with a conditional statement as @bwn explained but as well as testing for if the attribute doesn't have a value, the test checks whether @Value(Country)=@Value(feature[-1].Country)

takashi
Celebrity
  • takashiCelebrity
  • 7843 replies
  • April 28, 2020

Regarding "Confirmed", a possible way is to use a global variable containing the value of "Country" within its name, in order to save and retrieve the last Confirmed value for each Country.

Be aware that you have to set Across Output Ports to the Preserve Feature Order parameter in the Tester if you need to preserve the order of input features in the subsequent process.

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
mr_fme
Enthusiast
Forum|alt.badge.img+9
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • 145 replies
  • April 28, 2020

So a sorter prior to the attribute creator, then the attribute creator with a conditional statement as @bwn explained but as well as testing for if the attribute doesn't have a value, the test checks whether @Value(Country)=@Value(feature[-1].Country)

@ebygomm, unfortunately I was not successful

 

I have "confirmed" values not filled and the date field simply repeated. I also need to add an extra day on the previous date, if this field is empty.

 

 

 

 

 

Thank you so much

 

ebygomm
Influencer
Forum|alt.badge.img+44
  • ebygommInfluencer
  • Influencer
  • 3427 replies
  • April 28, 2020

@ebygomm, unfortunately I was not successful

 

I have "confirmed" values not filled and the date field simply repeated. I also need to add an extra day on the previous date, if this field is empty.

 

 

 

 

 

Thank you so much

 

You have the test condition incorrectly written for the Confirmed Attribute - you are testing @Value(Country) = @Value(feature[-1].Confirmed) which will never match.

For the date you need to change the attribute value to be @DateTimeAdd(@Value(feature[-1].Date),P1D)

This will add one day on to the previous value

mr_fme
Enthusiast
Forum|alt.badge.img+9
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • 145 replies
  • April 28, 2020

Regarding "Confirmed", a possible way is to use a global variable containing the value of "Country" within its name, in order to save and retrieve the last Confirmed value for each Country.

Be aware that you have to set Across Output Ports to the Preserve Feature Order parameter in the Tester if you need to preserve the order of input features in the subsequent process.

@takashi,

 

which version are you using? I still have problems see the images

 

 

Thank´s

 

takashi
Celebrity
  • takashiCelebrity
  • 7843 replies
  • April 28, 2020

Regarding "Confirmed", a possible way is to use a global variable containing the value of "Country" within its name, in order to save and retrieve the last Confirmed value for each Country.

Be aware that you have to set Across Output Ports to the Preserve Feature Order parameter in the Tester if you need to preserve the order of input features in the subsequent process.

Strange. I tested it with FME 2019.1.1, 2019.1.2, 2019.1.3.1, 2019.2.3.2, and 2020.0.1. What versions of FME are you using?

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
mr_fme
Enthusiast
Forum|alt.badge.img+9
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • 145 replies
  • April 28, 2020

You have the test condition incorrectly written for the Confirmed Attribute - you are testing @Value(Country) = @Value(feature[-1].Confirmed) which will never match.

For the date you need to change the attribute value to be @DateTimeAdd(@Value(feature[-1].Date),P1D)

This will add one day on to the previous value

Thank´s you helped me a lot. Solve my problem

mr_fme
Enthusiast
Forum|alt.badge.img+9
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • 145 replies
  • April 28, 2020

Strange. I tested it with FME 2019.1.1, 2019.1.2, 2019.1.3.1, 2019.2.3.2, and 2020.0.1. What versions of FME are you using?

@takashi, thank´s, my FME 2020.0.1.

 

Now work, very good!

 

Thank´s

Community Stats

32,076
Posts
121,794
Replies
39,702
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.