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;
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:
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 |
You could do it for more consecutive nulls by doing something similar in the load script:
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.
@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:
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 | - | - |
Here is an adjustment that worked for me:
@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;
@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 | - | - |
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.
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 |