Solved

How to fill empty values with previous values from same attribute

  • 12 November 2018
  • 5 replies
  • 17 views
C
Badge

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

icon

Best answer by takashi 12 November 2018, 14:42

View original

5 replies

takashi
Rank
Userlevel 2
Badge +17

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
Badge

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

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
Badge

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

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


Community Stats

28,707
Posts
109,469
Replies
36,549
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.