PHIL Sawyer
asked on
Fill Null values
Hi
I have a data set where I need the gaps filling in where the nulls are.
Select
Name,
Amt
from Table1
Required Data Set
Name Amt Name Amt
A null A 1 -- if on first row then back fill from first A value
A null A 1 -- if on first row then back fill from first A value
A 1 A 1
A 8 A 8
A 1 A 1
A null A 1 -- fill forward from last known value
A null A 1 -- fill forward from last known value
A null A 1 -- fill forward from last known value
A 8 A 8
B 1 B 1
B 2 B 2
B null B 2 -- fill forward from last known value
B 2 B 2
B 4 B 4
B null B 4 -- fill forward from last known value
C null C 2 -- if on first row then back fill from first C value
C 2 C 2
C 2 C 2
Regards
I have a data set where I need the gaps filling in where the nulls are.
Select
Name,
Amt
from Table1
Required Data Set
Name Amt Name Amt
A null A 1 -- if on first row then back fill from first A value
A null A 1 -- if on first row then back fill from first A value
A 1 A 1
A 8 A 8
A 1 A 1
A null A 1 -- fill forward from last known value
A null A 1 -- fill forward from last known value
A null A 1 -- fill forward from last known value
A 8 A 8
B 1 B 1
B 2 B 2
B null B 2 -- fill forward from last known value
B 2 B 2
B 4 B 4
B null B 4 -- fill forward from last known value
C null C 2 -- if on first row then back fill from first C value
C 2 C 2
C 2 C 2
Regards
Not possible. There is no column that can be ordered by so there is no ordering to determine first, next, last known, anything. Without a column to order by there is no guaranteed sort order so your query could produce different results every time.
I agree with above: unless you have a "time" or "row number" column value your request is not doable.
please clarify
please clarify
ASKER
There is a time value and didn't realize you would need it - see attached for full data set with time values.
Thanksexport.csvexport.csv
Thanksexport.csvexport.csv
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Johnsone - Brilliant and works a treat.
Thanks
Thanks