topic Data Preparation: Copy data for missing date from closest date before in App Development

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

noahfels — Fri, 03 Feb 2023 13:19:11 GMT

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!

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

Steven35 — Fri, 03 Feb 2023 15:26:44 GMT

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

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

noahfels — Fri, 03 Feb 2023 16:10:22 GMT

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.