Skip to main content
  • EN

I am trying figure out how to populate missing date records in a COVID database using the last known value.

Here is an example of input data:

CountryLast UpdateNumber of CasesMexico3/1/20205Mexico3/3/20205Mexico3/6/20206Mexico3/8/20207Mexico3/9/20207

 

I would like to generate a record for each day.

 

 

CountryLast UpdateNumber of CasesMexico3/1/20205Mexico3/2/20205Mexico3/3/20205Mexico3/4/20205Mexico3/5/20205Mexico3/6/20206Mexico3/7/20206Mexico3/8/20207Mexico3/9/20207

 

Any ideas on where I would start with this one?

Hi @scobierob You'll need an AttributeManager to get the date of the next record. Use a DateTimeCalculator to find the difference, clone by that, then recalculate the dates with a second DateTimeCalculator.

Hi @scobierob You'll need an AttributeManager to get the date of the next record. Use a DateTimeCalculator to find the difference, clone by that, then recalculate the dates with a second DateTimeCalculator.

Hi @danatsafe,

 

 

This brilliant. I've been testing it on my data but I noticed an error in the output of the workbench you provided.

 

 

The result of the workbench you provided is close but it is off by a little:

 

 

Mexico 02/29/2020 5

Mexico 03/01/2020 5

Mexico 03/01/2020 5

Mexico 03/02/2020 5

Mexico 03/03/2020 5

Mexico 03/05/2020 6

Mexico 03/06/2020 6

Mexico 03/08/2020 7

Mexico 03/09/2020 7

 

It seems that some dates are missing and some dates have been duplicated. Could this have something to do with a timezone setting?

Hi @scobierob You'll need an AttributeManager to get the date of the next record. Use a DateTimeCalculator to find the difference, clone by that, then recalculate the dates with a second DateTimeCalculator.

I would also like to avoid having dates for days that are precede the first date entry (ex: Mexico 02/29/2020 5 )

Cheers!

I would also like to avoid having dates for days that are precede the first date entry (ex: Mexico 02/29/2020 5 )

Cheers!

Hi @scobierob Sorry about that! In DateTimeCalculator_2 the Operator should be 'Add', not 'Subtract' Missing_days2.fmwt

Reply


Community Stats

30,176
Posts
114,544
Replies
37,837
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.