×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

replace null values with the last value other than null

replace null values with the last value other than null

replace null values with the last value other than null

(OP)
Hello!

how do I replace null values in a column with the last non-null value?

Ex:
Column Column
AB AB
null AB
CT CT
null CT
null CT
BH BH
null BH
null BH
SV SV
TM TM

Thank you!

RE: replace null values with the last value other than null

What do you mean by the "last" value? The value in the previous field? Or in the previous record? Or in the last record in the table?

The answer is probably that you should use NVL(), but you will need to clarify the question.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: replace null values with the last value other than null

(OP)
I have a table called
code abr
---------------
789 AB
789 null
789 null
801 CT
801 null
801 null
812 BH
812 null
812 null
816 SV
817 TM

And I want to select it so that I can fill in the blanks with the previous non-null value:

code abr
---------------
789 AB
789 AB
789 AB
801 CT
801 CT
801 CT
812 BH
812 BH
812 BH
816 SV
817 TM

RE: replace null values with the last value other than null

(OP)
Mike Lewis (Programmer) - in the previous record

RE: replace null values with the last value other than null

In T-SQL you could use window functions and when a column is NULL could look at LAG(column).

But when you scan a table it's simple to do, so this is a classic non-SQL task.

Cautious: This solution changes the data, it doesn't just set NULL values in a query result, they are permanently replaced. If you don't want that, you need to build up a result cursor instead of modifying the table.

CODE -->

SELECT yourtable
prevABR = ''
SCAN
   IF ISNULL(abr)
      REPLACE abr WITH prevABR
   ELSE
      prevABR = abr
   ENDIF
ENDSCAN 

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: replace null values with the last value other than null

CODE

SELECT myTable
DO WHILE .not. EOF()
	m.code = code
	m.abbr = abbr
	skip
	DO WHILE .not. EOF() .and. code = m.code
		replace abbr WITH m.abbr
		SKIP
	ENDDO
ENDDO 

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: replace null values with the last value other than null

Yes, both will work for your sample data.

My code takes it literally and puts in previous non-null ABR value on following rows, regardless of code. Also keeps all non-null values as is.
Griff harmonizes all rows with the same code.

If you're only interested in the CODE/ABR pairs without NULL in ABR, that'll be possible with SQL:

CODE

SELECT DISTINCT CODE, ABR FROM yourtable WHERE NOT ABR IS NULL 

Bye, Olaf.



Olaf Doschke Software Engineering
https://www.doschke.name

RE: replace null values with the last value other than null

Gabydrdoom,

If you have a code for the null values, you can UPDATE * FROM:

CODE --> VFP

CREATE CURSOR SampleData (Code Numeric(3), Abr Char(2) NULL)

INSERT INTO SampleData VALUES (789, 'AB')
INSERT INTO SampleData VALUES (789, NULL)
INSERT INTO SampleData VALUES (789, NULL)
INSERT INTO SampleData VALUES (801, 'CT')
INSERT INTO SampleData VALUES (801, NULL)
INSERT INTO SampleData VALUES (801, NULL)
INSERT INTO SampleData VALUES (812, 'BH')
INSERT INTO SampleData VALUES (812, NULL)
INSERT INTO SampleData VALUES (812, NULL)
INSERT INTO SampleData VALUES (816, 'ST')
INSERT INTO SampleData VALUES (817, 'TM')

UPDATE SampleData ;
	SET Abr = NN.Abr ;
	FROM (SELECT Code, Abr FROM SampleData WHERE Abr IS NOT NULL) AS NN ;
	WHERE SampleData.Code = NN.Code

BROWSE 

RE: replace null values with the last value other than null

(OP)
OK, thanks again for all your help. :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close