[Solved] How to use flow variables for row selection (range checking)

In this thread How do combine Processsteps ? I learned about flow variables which could support identifying parts of a time series in a table.
But this example show only use for pattern matching in the row filter.
As I want to extract a part of a time series I need to use the range checking (e.g. from 2018-06 until 2019-05)
But I did not find a possibility to select a flow variable in the range checking.

Is there a way to use flow variables for my application?

Pass variables to Date&Time-based Row Filter

2 Likes

@izaychik63,

Thank you for this help.
For the range I need both a lower and an upper limit (both are strings in my case)
In my case that’s a year’s period (1st of May to 30th of April).
With your help I can set two string input variables to these values.
But I have to assign them to two different nodes as I was only able to assign one variable to a node.
Furthermore I would like to calculate the second value rom the first one. It would be good enough if I can enter the year and calculate the limits as they are always from May 1st to April 30th (next year).

But this seems to be a very tricky thing in Knime.

You can use String Manipulation (Variable) node with join() function to combine parts of the date range you need.

Hi there @knimediger,

just to see if I got it right. So you would like to input a year (example 2018) and to filter your data set in order to leave rows with date values between 01.05.2018 and 30.04.2019.? If that is the case you should use String or Integer Configuration node for year input and after that create two flow variables in Variable Expressions node using two expressions. Those flow variables use in Date&Time-based Row Filter as suggested by @izaychik63.

If that is the case and you still can’t make it someone can create and example workflow. If I missed it somewhere feel free to additionally elaborate :wink:

Br,
Ivan

Hi @knimediger,

the example year and month you gave are strings as you noted. Youäve got to convert those into a valid date time first via String to Date&Time. Please note that your format is “yyyy-MM” which requires to adjust the node to parse a data only.

I assume the date range is dynamic as you wrote “lower and an upper limit”. If you use the min and max date values you’d simply get the very same list, though.

To give another example. To get the values of the last three recent month you might use:

  1. Sort (date descending)
  2. GroupBy to get unique values
  3. Row Filter to get the last three months
  4. GroupBy for min and max values
  5. Table Column or Row to Variable

What @izaychik63 showcased would be the next step. Making use out of the created variables.

What you meant with “Furthermore I would like to calculate the second value rom the first one. It would be good enough if I can enter the year and calculate the limits as they are always from May 1st to April 30th (next year).” is not fully clear. Can you add some example data so the desired result is easier to comprehend?

Cheers
Mike

1 Like

hi @mw,

Thank you for your additional support.

To give you a better understanding of my simple wish let me explain my wish.
I need to extract data between a time range of one year (201-05-01 - 2019-04-30 ).
Thanks to the explanations of @izaychik63 I learned how to add two flow variables.
But it’s quite nasty if I need to change two values if one would also do the job.
So entering one value (e.g. the start date 2018-05-01), calculate the second value from that and there will be no failure in entering the data.
And it could even more simplified: I enter just the year of start (2018), set the start date to May, 1st, calculate the end date and I’m happy.

Hi @knimediger,

you are welcome. Though, what you describe is a bit contradictory. If you are required to change to values you should not need to use variables at all. However, if you i.e. want to retrieve all values in a dynamic data range, i.e. beginning of May 2018 plus X month you might require variables.

It would be better if you could share example data that gives a better understanding of your intend / the desired results.

Kind regards
Mike

I was successful in identifying a solution for my wish:

To get the year I use an “Intger Input” node and create a variable called “int_StartYear”
This node is connected to a “Variable Expressions” node.
Two expressions calculate the start and end date:

str_StartDate=string(variable("int_StartYear")) + "-05-01"
str_EndDate=string(toInt(variable("int_StartYear")+1)) + "-04-30"

These variables are used as input for upper and lower bound (in the tab Flow Variables).

This allows me to evaluate a second EXCEL file using the same time range by using the same calculated variables.
Changes to the time range are now very easy and just in one place.

Thanks for guiding me to this solution.

Edit: This allow me also to create the title of my charts dynamicaly using the time range. Other big wish is fulfilled now.

1 Like

Hi @knimediger,

glad you made it!

Don’t know if you know about KNIME Hub. There you can upload your workflows for others to check them or learn from them :wink:

Br,
Ivan

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