Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Scenario 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 order by CustomerID and Date.
E.g.
Can anyone help me out here to achieve this.
Thanks in advance!!!
below is the solution.
noconcatenate
new_table:
load
CustomerID,
date,
sales_score as orginal_sales_score,
IF( ISNULL(sales_score) or sales_score = 0, PEEK(sales_score), sales_score) AS sales_score
resident table
order by CustomerID, date asc;
drop table table;
A:
Load *,
If (CustomerID = Previous(CustomerID),Previous(SalesScore),null()) as Previous_Score
resident Source order by CustomerID asc, Date asc;
noconcatenate
B:
Load CustomerId,Date,Coalesce(Sales_score,Previous_score) as sales_score resident A;
Drop table A;
HI @Gabbar ,
can you recheck the image I have reposted that
Outer join both Customer & sales_score tables and create a new resident table using the outer joined table.
Once done with that, incorporate highlighted changes from below image in your resident table and drop joined table:
Output:
Regards,
Aditya
below is the solution.
noconcatenate
new_table:
load
CustomerID,
date,
sales_score as orginal_sales_score,
IF( ISNULL(sales_score) or sales_score = 0, PEEK(sales_score), sales_score) AS sales_score
resident table
order by CustomerID, date asc;
drop table table;