Update Year in 1st Row

Question

Hi,

I have a below attached sample Data. I need to update the the 1st row of the F5 and F7 Field with the Year value at F6 that is 2022 and

F8 and F10 with the year Value in F9 that is 2021.

Note: The position of the column might change hence it need to dynamic and we cannot take the position as Fixed to be F6 or F7. Please help me out with this .

Update Year.yxmd

year Update.png

davidskaife · Accepted Answer

Hi @DataPirate26

Here is a solution that should work for you, it should also be dynamic so if the fields move it will still work as expected.

We separate out the first row, and Transpose it into two columns. Using the Multi-Row Formula tool we apply some checks using Regex where if the value is only a 4 digit number to fill in the row before and after.

IF REGEX_Match([Row+1:Value],'\d{4}')
THEN [Row+1:Value]
ELSEIF IsNull([Value]) AND REGEX_Match([Row-1:Value],'\d{4}')
THEN [Row-1:Value]
ELSE
[Value]
ENDIF

The additional check in the 'ELSEIF' section where it checks if the current row is null is to stop it flood filling all the remaining rows.

We then Cross Tab it back to the horizontal, union it with the original dataset (without the first row), then sort on RecordID to get back in the correct order!

Hope this helps