Automating Replacing Column/Row Values With Existing Data, Rule Engine?

[creedssmith]

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?

Thanks for your help slight_smile:

[ipazin]

Hello @creedssmith,

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:

replace or change of values in a column

Hello, in a dataset all wrong or missing values are filled by a wild fix number. I want to replace this number by an empty cell (or ? as missing value). Do you have an idea to do it? Thank you Axel

Br,
Ivan

[creedssmith]

@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.

So each missing data column draws from a different known data column each time.

[mlauber71]

@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.

Multiplying multiple columns with different columns in a table

I tried to set up da workflow where you have a ‘system’ of Variables and Coefficients that are identified by their name “Var1” and and addition of “_coeff” like in your example. The workflow is flexible so that it would take any number of columns of that structure and combine them (multiply in my example). If you know the operation maybe you could also put that in a variable and construct a formula with a simple Java Snippet within the loop. For the loop I have only found the solution to rename…

Automating Dynamic Variable Operations with Java Snippet in a Workflow Loop –...

Replace specific values with Missings - multiple columns at once - KNIME...

[creedssmith]

@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.