Skip to main content
Solved

Fill cell in Excel file based in previous information

mr_fme
Enthusiast
Forum|alt.badge.img+8

Hi all,

I have an excel file, where I need to fill in fields based on previous values. This should happen for 3 fields: total_suspects, total_positive, total_recovered, deaths. But some cells are not filled, they are empty. I need to fill in the previous amount, according to the previous date. Example: total_suspects for day 2020-04-06 3:00 is 19, but for day 2020-04-07, it is empty, so it should be 19. This same situation should continue in all empty fields in the sequence of days. This should happen for the 3 columns informed at the beginning. The problem is that this must happen grouped by name. However, each name set has the same date and different values.

Look below, my situation now:

 

Expectation:

 

My file below:

I have one sample workspace, with one sorter trasformer and one attribute creator with configuration below:

 

 

My workspace run very well, but for one location (name) only. My file has many lines, and many different locations (names), and in my output file nothing happens. can anybody help me?

 

Thank´s

 

Best answer by hollyatsafe

Hi @mr_fme,

Without seeing your workspace I can not be sure exactly what the issue is, I have taken the screenshots provided and this does appear to give me the result in you Expectation screenshot. If you're able to provide the workspace I think we'd be able to better provide assistance?

That being said I do have two comments that might help.

1. In the Sorter I think you'll also need to specify the date:

 

2. Since you are populating the attribute value based on the prior feature, if this one is missing it will not be correctly populated. Therefore you may want to build a second conditional statement, whereby if total_positive does not have a value, but the country is different from the previous one (e.g. first feature for new country is empty) give it a value of zero, this will ensure there are no gaps in the data meaning the result will look like:

 

Please find attached the workspace I was trying this out with. 112989.fmw

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

2 replies

H
Forum|alt.badge.img+2
  • hollyatsafe
  • Best Answer
  • April 29, 2020

Hi @mr_fme,

Without seeing your workspace I can not be sure exactly what the issue is, I have taken the screenshots provided and this does appear to give me the result in you Expectation screenshot. If you're able to provide the workspace I think we'd be able to better provide assistance?

That being said I do have two comments that might help.

1. In the Sorter I think you'll also need to specify the date:

 

2. Since you are populating the attribute value based on the prior feature, if this one is missing it will not be correctly populated. Therefore you may want to build a second conditional statement, whereby if total_positive does not have a value, but the country is different from the previous one (e.g. first feature for new country is empty) give it a value of zero, this will ensure there are no gaps in the data meaning the result will look like:

 

Please find attached the workspace I was trying this out with. 112989.fmw

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

Hi @mr_fme,

Without seeing your workspace I can not be sure exactly what the issue is, I have taken the screenshots provided and this does appear to give me the result in you Expectation screenshot. If you're able to provide the workspace I think we'd be able to better provide assistance?

That being said I do have two comments that might help.

1. In the Sorter I think you'll also need to specify the date:

 

2. Since you are populating the attribute value based on the prior feature, if this one is missing it will not be correctly populated. Therefore you may want to build a second conditional statement, whereby if total_positive does not have a value, but the country is different from the previous one (e.g. first feature for new country is empty) give it a value of zero, this will ensure there are no gaps in the data meaning the result will look like:

 

Please find attached the workspace I was trying this out with. 112989.fmw

Hi @hollyatsafe,

 

Fantastic!! It´s fine now!!

 

Thank you for you atenttion

Reply


Helpful Members This Week

Community Stats

31,176
Posts
118,181
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.