Pervious values without no showing nulls

[379SSS]

Hi All,

Senario is like I want all the sales_score values in customer table and if the sales_score is null it should pick pervious date sales_score value without null. Here it should be orderby CustomerID and Date.

Eg:   

Customer				sales_score
CustomerID	date			CustomerID	date	sales_score
1	28/9/2023			1	28/9/2023	Null
1	27/9/2023			1	27/9/2023	2
1	26/9/2023			1	26/9/2023	3
2	28/9/2023			2	28/9/2023	1
2	27/9/2023			2	27/9/2023	Null
2	26/9/2023			2	26/9/2023	4
2	25/9/2023			2	25/9/2023	Null
3	27/9/2023

Expected output.:                                                

customer

CustomerID	date	sales_score
1	28/9/2023	2
1	27/9/2023	2
1	26/9/2023	3
2	28/9/2023	1
2	27/9/2023	4
2	26/9/2023	4
2	25/9/2023	Null
3	27/9/2023	Null

Can anyone help me out here to achieve this.

Thanks in advance!!!

[KGalloway · Accepted solution]

I think I am misunderstanding something. It looks like it already does this:

with this script:

Set NullInterpret = '';

table:

load * inline [

CustomerID, date, sales_score

1, 9/28/2023

1, 9/27/2023, 2

1, 9/26/2023, 3

2, 9/28/2023, 1

2, 9/27/2023

2, 8/26/2023,0

2, 8/25/2023

2,  8/24/2023,4

3,  9/27/2023

]

;

noconcatenate

new_table:

load

CustomerID,

    date,

    sales_score as orginal_sales_score,

    if(sales_score > 0, sales_score, if(previous(CustomerID) = CustomerID, peek('new_sales_score'))) as new_sales_score

resident table

order by CustomerID, date asc;

drop table table;

[379SSS · Accepted solution]

@KGalloway  Thanks for your inputs here. It's working with your code and same with below which I have tried.

Set NullInterpret = '';
table:
load * inline [
CustomerID, date, sales_score
1, 9/28/2023
1, 9/27/2023, 2
1, 9/26/2023, 3
2, 9/28/2023, 1
2, 9/27/2023
2, 9/26/2023,0
2, 8/25/2023
2, 8/24/2023,4
3, 9/27/2023
]
;

noconcatenate
new_table:
load
CustomerID,
date,
sales_score as orginal_sales_score,
IF( ISNULL(sales_score) or sales_score = 0 , Peek(sales_score1), sales_score) AS sales_score1

resident table
order by CustomerID,date asc
;
drop table table;