Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
I think I am misunderstanding something. It looks like it already does this:
with this script:
@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;