# 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

ds4=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. Eg. in lines 2 and 3: 315 for B, in line 5: 128 and lines 7-9: 10.

The result should look like this:

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

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?

• Could you re-upload the image? Imgur reports broken file. – ercegovac Oct 11 '17 at 9:52
• Thank you sebhofer, for the edit! – Lea Oct 11 '17 at 15:11

I like your idea! You can do it in the following way

Module[{prev = Missing["Unmatched"]},
ds4[All, <|"ID" -> "ID", "Date" -> "Date", "A" -> "A",
"B" -> (If[#"B" === Missing["Unmatched"], prev, prev = #"B"] &)|>]]


or in a slightly modified version, which I personally like better (thanks to WReach to suggesting a simpler form of the query)

Module[{prev = Missing["Unmatched"]},
rfunc[Missing["Unmatched"]] := prev;
rfunc[b_] := prev = b;
ds4[All, {"B" -> rfunc}]
]


Also, I should point out that I'm on Mathematica version 10.0.2, that's why my query has the current form. From this answer I gather, that it could be rewritten in more convenient ways in other versions.

• +1 The query in your second alternative can be expressed in a simpler form: ds4[All, {"B" -> rfunc}]. – WReach Oct 11 '17 at 14:00
• @WReach Great, thanks for this suggestion! This was bugging me for quite a while actually! – sebhofer Oct 11 '17 at 14:37