I'd like to be able to filter one table into several output tables, based on a date column. The start date and end date for the filter evaluation are held in a second table.
I think the component parts are there (extract time window? row filter? rule based row filter? loops)
I'm having a blank on how to assemble into a work flow though!
Thanks for the info, but I don't think this is solution I need, unless I'm missing something?
I have two data tables;
The first table has a number of records (around 100K) with one column a date field.
The second table is a lookup table with around 30 records, where two columns are a start date and an end date.
I need to split the first table into n tables based on the date ranges.
The closest thing is the "Extract Time Window" node, but I need the "select starting point" and "select end point" to be supplied by the dates in the second table, maybe iterating through the table in a loop (somehow).
Unless the "Rule-Based Row Filter" or "reference based row filter" node can also have the date time passed to it?
You can. With String Manipulator you can create the proper conditions as strings based on the rows in your reference table (with Lag Column node) and convert that to flow variables using the Variable Loop (Data) meta node (or its components if it is required). Within the loop you can specify the flow variable to the filter value if you are using Row Filter, or use the Rule-based Row Filter with expression computed in the first step. I hope there is no hole in my idea. I'll try to create a workflow demonstrating what I mean if I was not clear.
If you could create an example workflow that would be great, as I'm not clear on how the Loop would work & how it would supply the two required dates to the query?
I am afraid my solution might be not the most pedantic, but I think it works. See the attached workflow. (It uses Java Snippets, QuickForms, KNIME 2.9, but none of them is a strong requirement I think. It was just easier for me to create it.)
Sorry, I guess I had a wrong version exported. Now I have added some notes too to make it easier to understand, modify. Probably the solution Iris linked to is a better one though.
Cheers, gabor
PS.: I hope this time I have uploaded the correct workflow and is close to your requirements.
Within the loop you get the output tables individually (at least that was my intention, I hope did not make a mistake again), after the loop end node these tables are collected, the different parts are labelled with the different iteration values.
I am afraid there are no nodes with arbitrary number of output ports. Probably it can be done with a custom node, but I do not know an option how to create such a node.
I am sorry I could not help solving your problem and misunderstanding your intention.
Do not worry, it was not grumpy. :) I understand it is annoying if the result differs from you have in your mind, I guess I misunderstood your question.
I'll try and add a csv writer node to the reference column filter, and a java snippet code node to pick up the iteration - and it will write one file out each time it loops through?
I know what I'll be doing at the weekend now!
If I get it to work I'll report back here early next week.