I have a sample dataset like this
Group Age Value
A 1
A 2 0
A 3 20
B 1 0
B 2 0
B 3 25
B 4 0
B 5 0
B 6 50
For each distinct value in Group
, replace null or zero Value
.
Ideal output will be
Group Age Value
A 1 20
A 2 20
A 3 20
B 1 25
B 2 25
B 3 25
B 4 25
B 5 50
B 6 50
UPDATE
This seems to do the trick
UPDATE test
SET [Value] = nz(DMin(
"[Value]",
"test",
"[Group] = '" & [Group] & "' AND
[Age] < " & [Age] & " AND
[Value] <> 0 AND [Value] IS NOT NULL"
),
DMax(
"[Value]",
"test",
"[Group] = '" & [Group] & "' AND
[Age] > " & [Age] & " AND
[Value] <> 0 AND [Value] IS NOT NULL"
))
WHERE [Value] = 0 OR [Value] IS NULL;
CodePudding user response:
You can use this (in MsAccess SQL a column can be derived from a subquery returning or one value - or no rows):
select
tactl.[Group],
tactl.Age,
( select trepl.[Value]
from test trepl
where tactl.[Group]=trepl.[Group]
and nz(trepl.[Value],0)<>0
and abs(tactl.Age-trepl.Age)=
( select MIN(abs(ta.Age-tb.Age)) as MinDistanceFound
from test ta
inner join
test tb
on (ta.[Group]=tb.[Group] and nz(tb.[Value],0) <> 0)
where (ta.[Group]=tactl.[Group] and ta.[Age]=tactl.[Age])
)
) as [Value]
from test tactl
GROUP
and VALUE
can be reserved words in most SQL implementations (they are in Access); try to use other words for them (I put them in brackets to overcome issues)
CodePudding user response:
Consider domain aggregation such as DMin
in order to have an updateable query:
UPDATE test
SET [Value] = DMin(
"[Value]",
"test",
"[Group] = '" & [Group] & "' AND
[Age] > " & [Age] & " AND
[Value] <> 0 AND [Value] IS NOT NULL"
)
WHERE [Value] = 0 OR [Value] IS NULL;