How to properly use multi row formula to populate the nulls

[johny9887]

Hi,

In the attached workflow below, I have 2 locations numbers (10001, 10002) with reporting date from 2021-07-31 to 2022-06-30. In the 1date file, there is only data for 2022-06-30 as it will continuously update from this point forward.

When i combined the 3 files together, the values for HR1, HR2 from 1date file was displaying as null except for the 2022-06-30 row as it was the only one with data. 

I want to be able to populate the nulls in HR1 and HR2 columns using the 2022-06-30 data for both 10001, 10002 locations meaning that:

- 10001 (HR1, HR2): will be populated with 0.1 (for HR1), 10 (for HR2) for all the nulls

- 10001 (HR1, HR2): will be populated with 0.5 (for HR1), 30 (for HR2) for all the nulls

I want it like this so that the past data is not null but going forward in the future, there will be data present to to populate the HR1, HR2 data for the future dates such as 2022-07-31 and onwards

Thank you

[DavidSkaife · Accepted solution]

Hi @johny9887 

You were almost there - if you add in a sort tool just before your multi-row formula tools, and sort it by LocationNumber (Ascending) & ReportingDate (Descending) you will have it populating correctly: