Question

Fill values automatically

mr_fme
Rank
Badge +6

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
Rank
Userlevel 4
Badge +30

Hello,

Please, could you share your data?

 

Thanks in Advance,

Danilo

mr_fme
Rank
Badge +6

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

B
Badge +3

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
Rank
Badge +6

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
Rank
Userlevel 1
Badge +21

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
Rank
Badge +6

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
Rank
Userlevel 1
Badge +21

 

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
Rank
Userlevel 2
Badge +17

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
Rank
Badge +6

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
Rank
Userlevel 1
Badge +21

@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
Rank
Badge +6

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
Rank
Userlevel 2
Badge +17

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
Rank
Badge +6

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
Rank
Badge +6

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


Community Stats

28,688
Posts
109,430
Replies
36,542
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.