filter data based on two dates in reference table question

Has anyone tried this? (I'm guessing folk have)

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! 

Any help/pointers much appreciated.

Cheers,

Andy

Hi Andy,

I am glad the Rule-based Row Filter handles this case. :) I guess the others also applicable. See the attached workflow.

The only trick is to use the ISO standard date format for filtering, like:

$Date and time$ < "2013-02-01T13:00:00" => TRUE

The format string is like this: yyyy-MM-DDTHH:mm:ss

Cheers, gabor

PS.: There are adventages living in a country where the standard time format is so close to the ISO standard. :)

Hi Gabor,

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?

 Cheers,

Andy

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.

Hi Gabor,

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?

Much appreciated!

Cheers,

Andy

Hi Andy,

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

Cheers, gabor

Hi Andy,

 

you could also use Aarons solutions from here http://tech.knime.org/forum/knime-general/quickforms-and-extract-time-window-node

 

Sorry gabor, but your solution didn't worked at all for me.

Hi Gabor,

I'm afraid it doesn't work for me either, I'm missing a node, called "Column Selection Quick Form"?  

Which package is that in and I'll look to install it?  Once I have it installed I'll try and step through it.

Iris, Thanks for the link, I'll have a look at Aaron's solution also, and see if I can step through that...

All fun and games! 

Hmm, it was working on my machine... Anyway, I'll check later with other.

The Column Selection Quick Form is in the "KNIME Nodes to create KNIME Quick Forms" feature (2.9.0).

Thanks Iris for the feedback and for the link to previous solutions.

Hi gabor,

I've downloaded the additonal nodes, but it's not working for me either.  Did you get a chance to re-look at it?

Cheers,

Andy

Hi Andy,

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.

Hi Gabor,

Thanks for uploading.  

I must be missing something though, as I just can't see what this is doing in relation to the question I asked? 

In which part of the process can I create 1 to n output tables of a table A based on the date range in a Table B?

I'll look at the other solution this weekend.

Cheers,

Andy 

hope that doesn't come across as grumpy by the way, it's not ment too!

Still "mentally moving" from SAS to KNIME, and learning all the time!

Hi Andy,

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.

Good luck, gabor

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.

ah....

then it may well work.

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.

Cheers,

Andy