Missing Values with previous row value

Hello 

I am new to KNIME and I have to do the following

ItemCode Date Collected Price Imputed Price
XYZ 1-Jan 12 12
XYZ 2-Jan   12
XYZ 3-Jan 12 12
ABC 1-Jan   16
ABC 2-Jan   16
ABC 3-Jan 16 16

As you can see above, I am collecting the price of 2 products from a retailer's website and there is missing data

1) If there is existing data and the ItemCode is the same, fill down the value. Ex: XYZ, 2-Jan

2)  If there is no existing data and ItemCode is the same, fill up the latest value. Ex: ABC 1-Jan, 2-Jan

 

Can you please help?

Thanks

Hi harish,

this is a little bit complex because of the rules with respect to previous rows. I attach you the simplest solution, which loops over the ItemCode and than fixes the missing value only in this group.

 

Another solution would be to use the LAG column node to shift the previous values into the same row and than use the rule engine node or the Column Merger node, to resolve the missing values.

I hope this helps!

Iris

Hi Iris,

Are you able to show how to do this using Lag Column Node?

Thanks!

I think the Missing Value node -> select previous value just solved my problem…

Thanksss…

1 Like

Hi there!

Glad you solved it. I would go with a Missing Value node here as well.

Additionally I have created a simple workflow that uses Lag Column combined with Rule Engine node just as example to check.

2019_03_25_Lag_Column_Node_Example.knwf (10.2 KB)

Don’t know your exact requirements so I have couple of notes for this and similar use cases:

  • in case your data is grouped like in above example you should use loop (Group Loop Start node) which will iterate over group of rows in order not to mix values in between groups
  • Lag option in Lag Column node needs needs to be set up according to the number of missing values possible in succession (this can be a bit tricky) in order not to replace missing values with just another missing value. It will produce multiple lagged columns and then logic is needed in Rule Engine node to take proper value
  • in case you need special logic about missing value like in example before (sometimes using previous and sometimes next row) two (or more) Missing Values nodes with different settings should be used

If any questions feel free to ask.

Br,
Ivan