Skip to main content
Solved

How to fill empty values with previous values from same attribute

  • November 12, 2018
  • 5 replies
  • 228 views
C
Forum|alt.badge.img

Hi,

I have an Excel reader and one column of the Excel files contains merged cells.

For an unknown reason, the option "expand merged cells" doesn't work well for some merged cells.

For some merged cells, I got all values in the expanded cells and for some others, I got only 2 values for 5 cells that were merged (for example).

(I tried to convert the Excel file in XLSX, XLS,.... the problem persists)

 

So, I try to find a solution to fill empty values with previous values from the same column (attribute).

I searched with the NullAttributeReplacer transformer or with a NullAttributeMapper but I don't find how to map to the previous value with this way...

 

One other difficulty is that it must work in "cascade": for example the empty value from row 3 must get the value from row 2 and then the empty value from row 4 must get the value from row 3, and so on...

 

Thanks for any help.

Christian

Best answer by takashi

Hi, have a look at the Adjacent Feature Attributes option in the AttributeCreator or the AttributeManager. See also this blog post to learn more: FME Adjacent Feature Attributes: An Example of Reading Structured Text Files

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

5 replies

takashi
Influencer
  • takashiInfluencer
  • Best Answer
  • November 12, 2018

Hi, have a look at the Adjacent Feature Attributes option in the AttributeCreator or the AttributeManager. See also this blog post to learn more: FME Adjacent Feature Attributes: An Example of Reading Structured Text Files

C
Forum|alt.badge.img
  • christian_b
  • Author
  • November 12, 2018
takashi wrote:

Hi, have a look at the Adjacent Feature Attributes option in the AttributeCreator or the AttributeManager. See also this blog post to learn more: FME Adjacent Feature Attributes: An Example of Reading Structured Text Files

Thank you Takashi, I saw this article a few minutes before your answer but I closed it because I thought "adjacent" was meant for geographic proximity.

Thank to you, I read this article carefully and it saved me.

Very powerfull this "Adjacent Feature Attributes" option !

 

Just one thing, I didn't find a solution to make it work in cascade mode, so I created 4 AttributeManager in serial (because there can be 4 blank cells maximum in my case) with conditional values:

1- If MyAttribute is missing --> Attribute Value: feature[-1].MyAttribute --> Output Attribute: MyAttribute2

2- If MyAttribute2 is missing --> Attribute Value: feature[-1].MyAttribute --> Output Attribute: MyAttribute3

3- If MyAttribute3 is missing --> Attribute Value: feature[-1].MyAttribute --> Output Attribute: MyAttribute4

4- If MyAttribute4 is missing --> Attribute Value: feature[-1].MyAttribute --> Output Attribute: MyAttribute5

 

Ideally, I would prefer a solution with one single AttributeManager that searches the last not empty value and that replace the value in the same attribute (not in a new attribute MyAttribute2, MyAttribute3,...)

 

But this is ok for this time ;)

 

takashi
Influencer
  • takashiInfluencer
  • November 12, 2018
takashi wrote:

Hi, have a look at the Adjacent Feature Attributes option in the AttributeCreator or the AttributeManager. See also this blog post to learn more: FME Adjacent Feature Attributes: An Example of Reading Structured Text Files

I think that a single AttributeManager with this conditional value setting can propagate the attribute value from one feature to the subsequent features which don't have a value in the attribute.

Output AttributeAttribute ValueMyAttributeIf MyAttribute is missing Then feature[-1].MyAttribute

 

Else <No Action>
C
Forum|alt.badge.img
  • christian_b
  • Author
  • November 13, 2018
takashi wrote:

I think that a single AttributeManager with this conditional value setting can propagate the attribute value from one feature to the subsequent features which don't have a value in the attribute.

Output AttributeAttribute ValueMyAttributeIf MyAttribute is missing Then feature[-1].MyAttribute

 

Else <No Action>

I tried this, that doesn't work because FME creates a second attribute MyAttribute (with the same name).

debbiatsafe
Safer
Forum|alt.badge.img+20

Hi @christian_b

I think the behaviour you are seeing where 'Expand Merge Cells' not working correctly is related to a bug with merged cell reading in FME 2018.

We've fixed this bug in our recent FME 2019 beta builds (19215 and higher). If you are able to, please try using a newer version of FME to read your file.

Reply


Helpful Members This Week

Community Stats

31,223
Posts
118,401
Replies
38,824
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.