Solved

Fill cell in Excel file based in previous information

  • 30 April 2020
  • 2 replies
  • 2 views
mr_fme
Rank
Badge +6

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

 

icon

Best answer by hollyatsafe 30 April 2020, 01:07

View original
2 Attachments

2 replies

H
Badge +2

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

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


Community Stats

28,729
Posts
109,527
Replies
36,565
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.