IF NULL (OR = 0 ) USE PREVIOUS DATE'S VALUE

[dominicmazvimavi]

 
Hi everyone
I would like to produce a table where by, I return the previous day's SALES VALUE, if 'current' date it is null. How would I go about it? 
I came up with the following, however not sure if it will return the previous date's value: 
if(isnull('SALES VALUE'),Previous('SALES VALUE'),'SALES VALUE') as 'SALES VALUE'. 

Please also consider if, there was been a NUL value for the previous two days  and the SALES VALUE that is available would then be from three days ago, that the script would be able to pick that value up. Thank you 

[PrashantSangle]

In script you can try with peek() . 

example

Test:
Load *,if(Sales_Value='NUL',Peek(Sales_Value),Sales_Value) as new_sales inline [
Date,Sales_Value
2023/04/01,10
2023/04/02,NUL
2023/04/03,20
2023/04/04,NUL
2023/04/05,30
2023/04/06,40
];

Regards,

Prashant Sangle

[dominicmazvimavi]

Hi PrashantSangle, thank you. Unfortunately that did not work. 

[Kushal_Chawda]

@dominicmazvimavi  you need to sort your data first to use previous and peek functions to work correctly

Data:
LOAD *
FROM Table;

New:
LOAD *,
if(isnull([SALES VALUE]),peek('SALES VALUE NEW'),[SALES VALUE]) as [SALES VALUE NEW]
resident Data
order by DATE;

drop table Data;

[PrashantSangle]

can you share the script, which you tried?? The above code is working fine at my end.

Regards,

Prashant Sangle

[dominicmazvimavi]

DATA:
LOAD [DATE],
     SALES_VALUE
FROM
datasource

NEW:
LOAD
DATE([DATE]) AS DATE,
     if(isnull(SALES_VALUE),Peek(NEW_SALES_VALUE), SALES_VALUE) as NEW_SALES_VALUE
   
Resident DATA
Order BY DATE;

DROP TABLE DATA;  

RESULT:

[PrashantSangle]

Try below

DATA:

Load *,

if(isnull(SALES_VALUE) or len(trim(SALES_VALUE))=0Peek(SALES_VALUE),SALES_VALUE) as new_sales;

LOAD [DATE],
     SALES_VALUE
FROM
datasource

Regards,

Prashant Sangle