Skip to main content
Question

Fill values automatically

mr_fme
Enthusiast
Forum|alt.badge.img+8

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

14 replies

danilo_fme
Evangelist
Forum|alt.badge.img+44
  • danilo_fmeEvangelist
  • Evangelist
  • April 27, 2020

Hello,

Please, could you share your data?

 

Thanks in Advance,

Danilo

mr_fme
Enthusiast
Forum|alt.badge.img+8
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • April 27, 2020

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

bwn
Evangelist
Forum|alt.badge.img+26
  • bwnEvangelist
  • Evangelist
  • 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+8
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • April 28, 2020
bwn wrote:

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+33
  • ebygommInfluencer
  • Influencer
  • April 28, 2020
mr_fme wrote:

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+8
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • April 28, 2020
ebygomm wrote:

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+33
  • ebygommInfluencer
  • Influencer
  • April 28, 2020
mr_fme wrote:

 

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
Influencer
  • takashiInfluencer
  • 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.

mr_fme
Enthusiast
Forum|alt.badge.img+8
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • April 28, 2020
ebygomm wrote:

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+33
  • ebygommInfluencer
  • Influencer
  • April 28, 2020
mr_fme wrote:

@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+8
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • April 28, 2020
takashi wrote:

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
Influencer
  • takashiInfluencer
  • April 28, 2020
takashi wrote:

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?

mr_fme
Enthusiast
Forum|alt.badge.img+8
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • April 28, 2020
ebygomm wrote:

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+8
  • mr_fmeEnthusiast
  • Author
  • Enthusiast
  • April 28, 2020
takashi wrote:

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

Reply


Helpful Members This Week

Community Stats

31,182
Posts
118,184
Replies
38,791
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.