Hello, we have a user that is wanting us to “unbox” groupings listed on the sheet attached under tab Price Authority details 1. Essentially, whenever there is a reference of “64200 CREST/MAN/DES MOINES” on the price authority details 1 tab, user wants to bring back the specific items listed within this group found on the tab Price Authority Groups. Would Knime’s looping system assist with that?
K_BNSF 4022 64200 (NM, TX) - 10.1.2021 - $300 Inc Lg Cap.xlsx (74.1 KB)
Hi @Einayyar and welcome to the forum.
In this case I don’t think you need a loop, just a way to parameterize the input so that a user can grab the specific rows they need. I put together a sample workflow that demonstrates the concept for you:
SearchStringsInExcelExample.knwf (70.9 KB)
Inside the component are just a few nodes to allow the user to input the column they want to search on, as well as the search string. Then the Row Filter node does the rest, using the flow variables provided:
Try it out and see what you think. Also if I misinterpreted what you’re trying to do, please let me know that too so we can find a different solution. 
Hello Scott!
Thank you for getting back to me so quickly! This is very cool and I think I can use this for something else I am working on, but in this instance, every time the source file states 64200 CREST/MAN/DES MOINES” they want that to be broken down to one row being “Des Moines, IA” next row to be “Manning, IA” following row, “Creston, IA” which is how the grouping is broken down in the price authority group.
The way I think of the ask is like a lookup filter excel function that not only creates new rows but then takes the data from the previous row and duplicates it with a new origin and or destination listed by grouping. I hope that helps. I attached a sheet that shows the filter function I did in Excel, but then realized it was going to work because of the data overlap.
BNSF 4022 K_64212 (INTO CA, AZ PVT EQUIP, SINGLE, 100S) - 10.1.2021 - $300 Lg Cap UT FLAT.xlsx (46.6 KB)
Based on your latest spreadsheet, it’s not clear to me what your desired inputs and outputs are. Are you trying to use KNIME to generate the pivot table in Sheet1 based on the Price Authority Details 1 sheet? Or do something else?
Sorry, I know its confusing. Its hard to write down what is being asked. They want me to unpack the groupings that RR put in origins and destinations so that they are listed out individually vs the grouping name. The grouping name and the associated origins and destinations are found under the price authority groups tab.
Furthermore, when we unpack the groupings they have to pull the data from the previous rows.
I put an example of what the end result kind of looks like in the attached spreadsheet under the tab “Knime Example”
BNSF 4022 64200 (NM, TX) - 10.1.2021 - $300 Inc Lg Cap.xlsx (75.1 KB)
Hi @Einayyar , may be you can give some examples along with your explanation, like sample input and how the expected output should be.
I made this much more complex then it had to be. It really is just a simple join with a table creator. I apologize. Thank you for all the help here and sorry to waste your time!
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
