Data Preparation: Copy data for missing date from closest date before

[noahfels]

Given the following input table:

I want to fill the missing dates (02.01.2023 and 03.01.2023) with the exact data (all rows) from the closest previous date (01.01.2023). All three rows with values 10, 20 and 90 should be created and added for 02.01. and 03.01. 

This should be done for every date without any data loaded. (Copy the data from the closest date above)

The expected output table looks like this:

Any ideas?

Thank you in advance!

[Steven35 · Accepted solution]

Hello,

I made a small script bellow(It can be optimized I did it quickly). I didn't do it, but you have to be careful that in the table you are working with, the data are sorted in ascending order of dates.

Data:
LOAD * Inline [
Date, Value
01/01/2023, 10
01/01/2023, 20
01/01/2023, 90
04/01/2023, 100
04/01/2023, 90
04/02/2023, 30
04/02/2023, 50
];

MinMax_tmp:
LOAD
  Min(Date) as MinDate,
  Max(Date) as MaxDate
;
LOAD
  FieldValue('Date', RecNo()) as Date   // allow to retrieve all Date_Key values present in the app
AutoGenerate FieldValueCount('Date')
;

LET v_MinDate = Peek('MinDate', 0, 'MinMax_tmp');
LET v_MaxDate = Peek('MaxDate', 0, 'MinMax_tmp');

Drop Table MinMax_tmp;

Map_Date:
Mapping Load distinct
	Date,
	Date
Resident Data;

Calendar_Tmp:
LOAD
	*,
    Applymap('Map_Date',Date,Null()) as [Close Date]
;
LOAD
  Date($(v_MinDate)+RecNo()-1) AS Date
AUTOGENERATE($(v_MaxDate)-$(v_MinDate)+1);

	
NoConcatenate
Data_Final:
LOAD
	Date,
    If(Len([Close Date])>0, [Close Date],Peek('Close Date')) as [Close Date]
Resident Calendar_Tmp;

Drop Table Calendar_Tmp;

Left Join(Data_Final)
LOAD
	Date as [Close Date],
    Value
Resident Data;

Drop Table Data;

Best regards,

Steven

[noahfels]

Thanks a lot! Seeing the finished solution now makes perfect sense. Works as intended.
For anyone trying this solution: Adapt your DateFormat in case it is not dd/mm/yyyy.