Skip to main content
2 of 7
added 2 characters in body
Lea
  • 942
  • 4
  • 12

Replacing "Unmatched" with value

I have two datasets

ds = Dataset@* Map[AssociationThread[{"ID", "Date", "A"} -> #] &]@{ {"C12", SQLDateTime[{2012, 12, 11, 0, 0, 0.}], 10}, {"C12", SQLDateTime[{2015, 2, 19, 19, 30, 0.}], 9}, {"C12", SQLDateTime[{2013, 1, 22, 0, 0, 0.}], 12}, {"C10", SQLDateTime[{2013, 1, 28, 9, 25, 0.}], 15}, {"C10", SQLDateTime[{2014, 3, 21, 20, 0, 0.}], 12}, {"C11", SQLDateTime[{2014, 8, 27, 6, 40, 0.}], 8}}

and

ds2 = Dataset@* Map[AssociationThread[{"ID", "Date", "B"} -> #] &]@{ {"C10", SQLDateTime[{2013, 1, 28, 0, 25, 0.}], 315}, {"C11", SQLDateTime[{2013, 3, 2, 6, 40, 0.}], 128}, {"C12", SQLDateTime[{2012, 10, 10, 0, 0, 0.}], 10}}

and I combine it via

SortBy["ID"]@JoinAcross[ds, ds2, {"ID", "Date"}, "Outer"]

Now, I want to replace the unmatched B values with the last (according to date) occurring B value of the matching ID as shown here

sorry, something wrong with files

I tried something like this, where I replaced the Missing["Unmatched"]->0

Module[{prev = #"B" &}, ds4[All, {If[#"B" == 0, <|#, "B" -> #"B" + prev|>, #], prev = #"B"}[[1]] &]]

But it doesn't yield the desired result. It only replaces the first occurrence. Any ideas?

Lea
  • 942
  • 4
  • 12