My question is: instead of stringing 25 Rule Engine adjustments (one for each missing data column), is there a way to better automate replacing the missing data?

I have a series of 74 categories of data using short-term data (3 year range) and separately long-term data (10 year range), so I can perform percentage change and rate of change analyses between the ST and LT data (easy, math formula).

However, 2 of the 74 categories do not have long-term data; they’ve only existed long enough to populate short-term data.

Therefore, I get missing (“?”) data in a series of 25 or so columns where the long-term data does not exist. Using the Missing Value node I replaced the missing data with a number I don’t expect to ever populate (25,000,009).

I can now switch the dummy data with the actual short-term data so my percentage change and rate of change calculations show no change instead of blowing up due to missing data or a zero.

My question is: instead of stringing 25 Rule Engine adjustments (one for each missing data column), is there a way to better automate replacing the missing data?

if I got it right you are applying same logic in all 25 columns. Then you can either loop or go with unpivoting-apply logic-pivoting approach which should be faster and more straightforward. Take a look here:

@ipazin I’ve studied both proposed solutions and I’m not sure I can make them work.

For more details: This table has 74 rows (one for each category), 119 columns (1 key and 118 data), 2 sets of data being short term 59 columns, and long term 59 columns.

Because two rows (categories) are missing long term data I am replacing the missing long term data with existing short term data.

I have to populate the missing data in column 119 with known data in column 32 in once example.

@creedssmith maybe you could use a loop. You have a list which two pairs of columns would be needed to fill the not existing one. Then in the loop you rename both to a standard value (_x_source, _y_target), then do the rule with the standard names, rename them back and collect the results.

@mlauber71, ah–I think I see where you are going here. So essentially any of 59 known data columns can be associated with any one of 59 columns of missing data–and thus replace them.

Now, I’m only replacing two rows of seventy-four of the deficit columns. Let me see if I can integrate your solution without hurting myself… Without having played with this yet, perhaps I’ll need to use a Row Filter to split out the missing data rows and then run all?

Thank you for the extensive assistance and kindness.