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]

While your table is ordered, you can use the below function to grab a value in a row below the current row.

So, you could use something like coalesce(sales_score, below(total sales_score, 1)) in the chart.

Here is an example:

[379SSS]

HI @KGalloway , 

 You're right! but when I have two or more consecutive Null and I want to pick which is not null and Zero. How to get this? Let say example here.

customer:                                                           sales_score                                                                     outputtable

ID1	DATE4	?		ID1	DATE1	SCORE1		ID1	DATE4	SCORE4
ID1	DATE7	?		ID1	DATE2	NULL		ID1	DATE7	SCORE5
				ID1	DATE3	SCORE3
				ID1	DATE4	SCORE4
				ID1	DATE5	SCORE5
				ID1	DATE6	NULL
				ID1	DATE7	NULL
				ID1	DATE8	SCORE8
				ID1	DATE9	SCORE9
				ID1	DATE10	SCORE10
				ID1	DATE11	SCORE11

[KGalloway]

You could do it for more consecutive nulls by doing something similar in the load 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, 9/26/2023, 4

2, 9/25/2023

2,  9/24/2023

3,  9/27/2023

]

;

noconcatenate

new_table:

load

CustomerID,

    date,

    sales_score as orginal_sales_score,

    if(sales_score, sales_score, if(previous(CustomerID) = CustomerID, previous(sales_score))) as sales_score

resident table

order by CustomerID, date asc;

drop table table;

Here, the second table loads the data ordered by ID and date ascending. It then creates the sales_score field by taking the value from sales_score if it exists or the previous sales_score if it doesn't (as long as the IDs match).

Here is the result of that:

You can change the sort direction from asc to desc depending on how you want the nulls to be populated (from the earlier date or the later date, respectively).

Let me know if I can clarify anything.

[379SSS]

@KGalloway  thanks for your time.
Its work for some extent. But my question is I want to populate all the values without null and zero's, hope you understand with below example.

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, 9/25/2023

2,  9/24/2023,4

3,  9/27/2023

]

;

The out I need is like below.

CustomerID	date	orginal_sales_score	sales_score
1	9/26/2023	3	3
1	9/27/2023	2	2
1	9/28/2023	-	2
2	9/24/2023	4	4
2	9/25/2023	-	4
2	9/26/2023	0	4
2	9/27/2023	-	4
2	9/28/2023	1	1
3	9/27/2023	-	-

[KGalloway]

Here is an adjustment that worked for me:

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, 9/25/2023

2,  9/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]

@KGalloway  Also I tried below this, it's working.
noconcatenate
new_table:
load
CustomerID,
date,
sales_score as orginal_sales_score,
IF( ISNULL(sales_score) or sales_score = 0, previous(sales_score), sales_score) AS sales_score

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

[379SSS]

@KGalloway 
If I have different month with same ID, it should pick as below but the above two solutions are not working for this.

CustomerID	date	orginal_sales_score	sales_score
1	9/26/2023	3	3
1	9/27/2023	2	2
1	9/28/2023	-	2
2	8/24/2023	4	4
2	8/25/2023	-	4
2	8/26/2023	0	4
2	9/27/2023	-	4
2	9/28/2023	1	1
3	9/27/2023	-	-

[KGalloway]

So the record with CustomerID = 2 and date = 9/27/2023 should be 1?

I think something can be done to allow for that. You would need an additional condition in the if statement. You would also need to "do it again backwards" so that the sales_score from a later date could go to an earlier date.

[379SSS]

It should be like below table based on ID it should pick the values which exclude nonzero and nulls. Date could be anything, but it should take previous nonzero values of same ID as shown in below.

2	8/24/2023	4	4
2	8/25/2023	-	4
2	8/26/2023	0	4
2	9/27/2023	-	4