Lookup Value +1 Month and Carryforward Result

Hi everyone, been a KNIME user since a year. Currently can’t figure my head around a problem, please help!

My dataset is such:

Country: USA
Date: 2018-January-01
Comment: Goods Delivered

Now I have another entry as such:

Country: USA
Date: 2019-February-01
Comment: (blank)

Now I want the comments from January to be carry forwarded to February. And if we had March dataset instead of Feb then they should be carry forwarded to March. The condition is that we have to search last 3 months from Max Date. If we had latest date of April then we should first search in March. If record not found then search in Feb. If still not found then search in Jan. If record found in max of 3 months then the comments should be carry forwarded else leave it blank.

Please help!! :frowning:

Hi @Fahd_Aly_K , welcome to the forum! Can you attach the raw data, or upload the workflow that you’ve attempted? This case is definitely solvable with KNIME.

1 Like

Hi Badger! Thanks for reaching out. Here is a sample 2 row table:

image

I am using Column Expressions node for this but am unable to work the logic. Please let me know if you need any more information. Note: lookup key would be Country, Category, Brand. Approach should be calculating Max Date and then looking 3 months back from that date and if any record found we should copy/paste its comments to the max date row.

Once again thanks for your help!!

Thanks, but I’ll need to have the data for me to have something to work with. Can you upload at least the first 20 rows or so? You can transform any sensitive data to a dummy substitute, as long as they represent the same format & pattern as the original strings.

Note: By upload, I meant to refer to the file itself, not a screenshot. Thanks!

2 Likes

Hey Badger! Please find below sample data in Excel format. Let me know if this works?

Sample Data.xlsx (9.4 KB)

Lookup ID should be (Country, Category, Brand, Copy) … We have to lookup Max Date and carry forward comments in the last 3 months. Excited for your attempt!!

Hi @Fahd_Aly_K,

For the last row, which is supposed to belong to the same identifier columns as the previous 2 rows before it - the year was 2020 as opposed to what I think should be 2022. Was it a typo?

1 Like

Hi @badger101 ,

No that was intentional … That row is basically useless since it is outside the 3 month from Max Date of identifier time window… i hope you understand.

Hey @badger101, this is what the final table should look like:

Sample Data - Solution.xlsx (9.4 KB)

Comment in row 7 are blank since its last data was outside of 3 month window.

Thank you. My outcome is exactly like the solution you provided:

The column ‘Comments (#1)’ is the old column, and the last column entitled ‘Comments’ is what I came up with, similar to yours. Can you validate this before I publish the workflow?

1 Like

Wow thank you so much @badger101!! I made this account today and you provided me solution in only a few hours … This is perfect! Please share how you did it!!

@Fahd_Aly_K here’s my attempt at your case:

And here’s your workflow:
Lookup Value from Previous Month (up to max of 3).knwf (96.5 KB)

Here’s a snapshot of the final result:

Disclaimer:
Working with loops & using the Transpose Node are suitable for tables that don’t have too many rows (eg. 5-figures and above). I hope your true raw data is not too large :smile: :ok_hand:

1 Like

UPDATE:

My apologies, please use this edited workflow instead:

My previous workflow had an incorrect configuration which was overlooked until I did a revised quality checking.

Here’s the new one:
Lookup Value from Previous Month (up to max of 3).knwf (108.6 KB)

(The results remain the same with the sample data but won’t be the same with a modified data, hence this revised workflow is the one to choose).
Thank you!

3 Likes

This is excellent @badger101!! Thank you so much!!! I will try this on my actual dataset and update you with the results!

1 Like

Sure thing. I’ll be here for further follow up questions or any issues that may arise.

1 Like

Hey @badger101! Just to update you- I tweaked the workflow slightly and it works like magic on my actual dataset :slight_smile:

Once again, thank you so much for your time and help on this ^^

Side question: how do you make those wide arrow connections from excel reader to joiner and column appender?

1 Like

Glad to hear that @Fahd_Aly_K !

For your latest question: You can click on the arrow itself, then you’ll see one point in the middle where you can click to drag anywhere you like. If you drag it far enough, I think 2 more points will appear on the left and right. The best way to explain this is through a video though (or a meme-like image), but I hope it’s sufficient :smile:

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.