Skip to main content
Solved

Replacing null value with non-null value in a column


syedh2993
Contributor
Forum|alt.badge.img+3

Hi All,

I would require your help dealing with empty values in a column

I have a file with "n" features. In one of the column (Example: Column A) certain values are null/empty. 
I would like to replace it with non-empty value. Also, Column A has only 2 distinct values - either null or date.

Example:

Feature     Column A
1            Jan 2025
2            Jan 2025
3            Jan 2025
4            null
5            null
6            null
7            Jan 2025
8            Jan 2025

I would like to change Feature 4,5,6 to "Jan 2025" instead of null.

In Java world, I would store them in local variable and run column A in loop and replace with value if null.
Not sure how to perform similar scenario in FME. Would really appreciate your help in achieving the above expected output.

Thanks in advance

Best answer by hkingsbury

you need to reference the same field you’re updating. In the above example:

  • Feature 1 has a date - do nothing
  • Feature 2 is null, look at date, date is Jan 2025, set new date to Jan 2025
  • Feature 3 is null, look at date, date is null, set new date to Jan 2025
  • ….

If you want them to populate in new_date, the easiest way would be to copy date to new_date first using an AttributeCopier

View original

hkingsbury
Celebrity
Forum|alt.badge.img+50

Have a look at the NullAttributeMapper. This will allow you to replace attributes with a defined value (in your case null) with another value.

https://docs.safe.com/fme/html/FME-Form-Documentation/FME-Transformers/Transformers/nullattributemapper.htm

 

 

EDIT:
 

  

Thinking about the above, that is fine if you’re expecting to replace all nulls with the same value.  However if you data is more similar to the below, where it is ordered and you have some rows missing the date, that first approach won’t work.

Feature

Date
1 Jan 2025
2 Jan 2025
3 null
4 Feb 2025
5 Mar 2025
6 null
7 null
8 Apr 2025
9 null

 

In this scenario you can leverage adjacent features - https://fme.safe.com/blog/2017/08/adjacentfeatureattributes-evangelist166/

In a AttributeCreator, enable adjacent features

This now allows you to get the value from the previous feature that has passed through the transformer. Add an Output Attribute called date with a conditional value
 

 

In the conditional value we want to check if the current value is null, if it is, take the previous features date. If the value is not null, take no action
 

 


syedh2993
Contributor
Forum|alt.badge.img+3
  • Contributor
  • January 13, 2025

Thank you so much for taking time to respond to my question. 

Attribute creator works well if there is a non-null value in previous feature

However, if there are 2 or more null values next to each other, the result of new attribute is null as well

Example:

Feature        date        new_date
1            Jan 2025    Jan 2025
2            null        Jan 2025
3            null        null
4            Jan 2025    Jan 2025
5            null        Jan 2025

In the above example - "date" is existing column and "new_date" is the column I created using Attribute creator
"new_date" value for feature 3 is null because date value of feature 2 (previous feature) is null


1) Any thoughts on how I can handle this ?

2) Also, Just for your information, values in "date" column will be the same. it will not have different date. 
Is there any way to store non-null value in random using some variable and replace the null columns ?


Thanks,


hkingsbury
Celebrity
Forum|alt.badge.img+50

you need to reference the same field you’re updating. In the above example:

  • Feature 1 has a date - do nothing
  • Feature 2 is null, look at date, date is Jan 2025, set new date to Jan 2025
  • Feature 3 is null, look at date, date is null, set new date to Jan 2025
  • ….

If you want them to populate in new_date, the easiest way would be to copy date to new_date first using an AttributeCopier


virtualcitymatt
Celebrity
Forum|alt.badge.img+34
syedh2993 wrote:

 

2) Also, Just for your information, values in "date" column will be the same. it will not have different date. 
Is there any way to store non-null value in random using some variable and replace the null columns ?


Thanks,

I think in this case you want to use the NullAttributeMapper as ​@hkingsbury initially suggested

 

 


Reply


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