# dynamic column filtering . . . **URL:** \ **Category:** KNIME Analytics Platform\ **Created:** [May 5, 2021, 11:48am UTC](https://forum.knime.com/t/dynamic-column-filtering/32729 "2021-05-05T11:48:55Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![sanderlenselink](https://forum-cdn.knime.com/user_avatar/forum.knime.com/sanderlenselink/32/23000_2.png) [@sanderlenselink](https://forum.knime.com/u/sanderlenselink)\ **Post date:** [May 5, 2021, 11:48am UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/1 "2021-05-05T11:48:56Z")
Hi all, I have a semi-structured dataset. It consists of 500+ rows and 100+ variables. As you can see below there is some structure; the YEAR preceeds a fixed number of variables (in the example 4 variables). The values of the variables are string type or missing). ![image](https://forum-cdn.knime.com/uploads/default/original/3X/7/9/791a08bacd8062a37d68c76b45af70cdb22b6805.png) I puzzled for a while to filter dynamically the right columns. For example to retrieve all the data regarding e.g. 2020. or 2022. See the screenshot below. Is there someone with an idea to get this done? Please find attached the example-dataset ![image](https://forum-cdn.knime.com/uploads/default/original/3X/2/0/2080fc38cad10a5cbc5d9a7e48a5797ff6c941f4.png) [KNIME\_column\_filtering\_dynamically.xlsx](https://forum.knime.com/uploads/short-url/niUizPAcFSCDV4EEAEVlVy9qwQ7.xlsx) (10.8 KB) ---
**Author:** ![bruno29a](https://forum-cdn.knime.com/user_avatar/forum.knime.com/bruno29a/32/28802_2.png) [@bruno29a](https://forum.knime.com/u/bruno29a)\ **Post date:** [May 5, 2021, 12:33pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/2 "2021-05-05T12:33:21Z")
Hi @sanderlenselink , do all the sets have 4 variables each? In other words, is it always: Year\_(x), var#1, var#2, var#3, var#4 ? Do any of these headers repeat on other rows? ---
**Author:** ![sanderlenselink](https://forum-cdn.knime.com/user_avatar/forum.knime.com/sanderlenselink/32/23000_2.png) [@sanderlenselink](https://forum.knime.com/u/sanderlenselink)\ **Post date:** [May 5, 2021, 1:20pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/3 "2021-05-05T13:20:55Z")
Hi Bruno, all sets have 6 variables each (for simplicity reasons I took 4 variables in the example). Every header/variable is unique (see screenshot from on the real data, hopefully that helps. ) ![image](https://forum-cdn.knime.com/uploads/default/original/3X/6/0/6025f2effaa5d17cf987eddb0c2909db27493e74.png) ---
**Author:** ![sanderlenselink](https://forum-cdn.knime.com/user_avatar/forum.knime.com/sanderlenselink/32/23000_2.png) [@sanderlenselink](https://forum.knime.com/u/sanderlenselink)\ **Post date:** [May 5, 2021, 1:25pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/4 "2021-05-05T13:25:00Z")
. . . the latest screenshot is with 3 variables. Don’t let that be confused (otherwise the picture become very confusing/complicated) ---
**Author:** ![bruno29a](https://forum-cdn.knime.com/user_avatar/forum.knime.com/bruno29a/32/28802_2.png) [@bruno29a](https://forum.knime.com/u/bruno29a)\ **Post date:** [May 5, 2021, 2:16pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/5 "2021-05-05T14:16:50Z")
Hi @sanderlenselink , since you have a fixed number of columns in a set, I think the logic would be to move the horizontal data into vertical, where you would end up with 8 columns in total: Company, Year, and the 6 vars I believe the unpivot node can help with this - I understand what pivot and unpivot can do, but I don’t quite understand how to use them (I’m not an Excel user per se 🙂 ) > **[Unpivoting](https://hub.knime.com/knime/extensions/org.knime.features.base/latest/org.knime.base.node.preproc.unpivot2.Unpivot2NodeFactory)** > > This node rotates the selected columns from the input table to rows and duplicates at the same time the remaining input columns by appending them to each corre… Once you have the data in this form, you can then do row filter based on year, and/or on company as you like. ---
**Author:** ![bruno29a](https://forum-cdn.knime.com/user_avatar/forum.knime.com/bruno29a/32/28802_2.png) [@bruno29a](https://forum.knime.com/u/bruno29a)\ **Post date:** [May 5, 2021, 3:30pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/6 "2021-05-05T15:30:32Z")
Hi @sanderlenselink , as I mentioned, I am not too familiar with working with pivot/unpivot, so I have tried an alternative way via transpose to reach the unpivot results. In this case, I want to transpose every 5 columns (every 7 columns in your real dataset) AFTER the Company column. I was trying to do this with Chunk Loop. I know how to use it with rows, but was not sure how to proceed with columns, and because of this, I transpose the whole data set so that all columns would become rows, and then proceeded transposing by chunks. This is what the workflow looks like: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/9/7/97ef55d38aad0b890c36f009aee747d0463c070d.png) From the Excel Reader (basically the excel you provided): ![image](https://forum-cdn.knime.com/uploads/default/original/3X/4/5/45c10ca7c0f1bc9136df018ae17a4f0dc134e77a.png) Result at the end of the loop (giving what unpivoting would give): ![image](https://forum-cdn.knime.com/uploads/default/original/3X/2/f/2ff208f54205b0c3f9cd86442719f76eb2088789.png) After that, you can filter as you like, in this case I filtered on year 2018. You can do this via a row filter, or this can also be done interactively with a table view. From Row filter: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/0/9/09eb0a42addcc0fb26633e0d11ef2895bf0b25ec.png) From Table View: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/0/e/0e9ebbbd19e37272e0753c38e223ce6b84160b92.png) ![image](https://forum-cdn.knime.com/uploads/default/original/3X/d/c/dcdb898176be206f305f865dbc9354489a39fa90.png) Here’s the workflow: [Dynamic Column Filtering.knwf](https://forum.knime.com/uploads/short-url/h4CeUmlz6z2M8DRkH0J4nIAqRxw.knwf) (41.9 KB) Note, if you have more variable columns in your real dataset as you mentioned, simply change the chunk size from the Chunk Loop: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/8/b/8b9ac691f5aa7941579231f9947baacd619eb420.png) ---
**Author:** ![ipazin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ipazin/32/806_2.png) [@ipazin](https://forum.knime.com/u/ipazin)\ **Post date:** [May 6, 2021, 10:24am UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/7 "2021-05-06T10:24:19Z")
Hello @sanderlenselink, here is how it would work with Unpivoting. The upper part is only necessary if the number of value columns is changing. [Dynamic Column Filtering\_ipazin.knwf](https://forum.knime.com/uploads/short-url/o177VSDXiqdrO6qCSJ6taRsOhHJ.knwf) (30.8 KB) Br, Ivan ---
**Author:** ![Daniel\_Weikert](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e47c2d/32.png) [@Daniel\_Weikert](https://forum.knime.com/u/Daniel_Weikert)\ **Post date:** [May 6, 2021, 5:33pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/8 "2021-05-06T17:33:42Z")
Thanks @ipazin . I used window loop + moving aggregation for the first part of your workflow to see if that works too. Sometimes I got the feeling that 99% of questions can be answered with unpivot or regex 😂 best regards ---
**Author:** ![ipazin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ipazin/32/806_2.png) [@ipazin](https://forum.knime.com/u/ipazin)\ **Post date:** [May 7, 2021, 2:50pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/9 "2021-05-07T14:50:01Z")
Hello @Daniel_Weikert, you are welcome. Did window loop + moving aggregation worked? Mind sharing workflow to check it out? (Un)pivoting is really powerful operation when doing above and similar data transformations. And big plus is that it usually replaces long lasting loops. And regarding regex although I’m not a big fan of it have the same feeling 😃 Br, Ivan ---
**Author:** ![Daniel\_Weikert](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e47c2d/32.png) [@Daniel\_Weikert](https://forum.knime.com/u/Daniel_Weikert)\ **Post date:** [May 7, 2021, 3:07pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/10 "2021-05-07T15:07:24Z")
I am very sorry I did not save it (will do next time so more people benefit from it). Yes it worked. The main idea was just to see whether I can reproduce the 1-5 counter you implemented in your workflow. best regards and enjoy your weekend ---
**Author:** ![ipazin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ipazin/32/806_2.png) [@ipazin](https://forum.knime.com/u/ipazin)\ **Post date:** [May 7, 2021, 3:33pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/11 "2021-05-07T15:33:35Z")
Ok. I see now what you meant by “first part of workflow”. Had a trouble with this part as couldn’t find easy way to determine wanted number… Tnx and you too! Ivan ---
**Author:** ![sanderlenselink](https://forum-cdn.knime.com/user_avatar/forum.knime.com/sanderlenselink/32/23000_2.png) [@sanderlenselink](https://forum.knime.com/u/sanderlenselink)\ **Post date:** [May 12, 2021, 1:47pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/12 "2021-05-12T13:47:28Z")
Hi all, many thnx for the ideas. They worked for me. The idea of @Daniel_Weikert . . . can you explain a bit more in detail what you did? I looked at window-loop and moving-aggregation but I don’t see the clue (that’s fully my “mistake”) -Sander ---
**Author:** ![Daniel\_Weikert](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e47c2d/32.png) [@Daniel\_Weikert](https://forum.knime.com/u/Daniel_Weikert)\ **Post date:** [May 13, 2021, 1:35pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/13 "2021-05-13T13:35:06Z")
all I did was just borrowing a part @ipazin nice workflow for educational purposes and see whether I can get the repeating count to 1-5 with the two nodes ![window](https://forum-cdn.knime.com/uploads/default/original/3X/3/7/374525fe43ca103ddfb07ba1c06d6579e1b27d85.png) ![windoww](https://forum-cdn.knime.com/uploads/default/original/3X/5/2/52cd5ef7e31209cb090a68a581a99d1ad2fb2fd9.png) So it was only a part of the workflow. Hope that helps br ---
**Author:** ![sanderlenselink](https://forum-cdn.knime.com/user_avatar/forum.knime.com/sanderlenselink/32/23000_2.png) [@sanderlenselink](https://forum.knime.com/u/sanderlenselink)\ **Post date:** [May 17, 2021, 2:05pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/14 "2021-05-17T14:05:29Z")
@Daniel_Weikert THNX See the attached workflow where integrated the idea’s of bruno29a, ipazin and daniel. [SL\_KNIME Dynamic Column Filtering\_ALL\_DEFDEF 20210517.knwf](https://forum.knime.com/uploads/short-url/fGpDckxyyL0CPELGoz5VLGeUvIC.knwf) (64.6 KB) So, 3 different approches produce the same result. For me an import learning to understand these methods. In general I prefer short code and the method of daniel is really short. THNX -Sander ![image](https://forum-cdn.knime.com/uploads/default/original/3X/e/1/e1b334e5bb213907cae44cfa6948c445457c6ee6.png) ---
**Author:** ![Daniel\_Weikert](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e47c2d/32.png) [@Daniel\_Weikert](https://forum.knime.com/u/Daniel_Weikert)\ **Post date:** [May 17, 2021, 4:41pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/15 "2021-05-17T16:41:36Z")
Great work and thanks for sharing @sanderlenselink . This is certainly helpful for everyone here so your contribution is highly appreciated. best regards ---
**Author:** ![sanderlenselink](https://forum-cdn.knime.com/user_avatar/forum.knime.com/sanderlenselink/32/23000_2.png) [@sanderlenselink](https://forum.knime.com/u/sanderlenselink)\ **Post date:** [May 19, 2021, 8:23am UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/16 "2021-05-19T08:23:28Z")
Hi all, please find attached a renewed workflow with the 3 methods (I made a mistake coping the 3rd method of @bruno29a . . . grrrrr.) [SL\_KNIME Dynamic Column Filtering\_ALL\_DEFDEF 20210519.knwf](https://forum.knime.com/uploads/short-url/rDkWU4Htggw6A5xWudk7LRwx4V7.knwf) (69.5 KB) -Sander ---
**Author:** ![ipazin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ipazin/32/806_2.png) [@ipazin](https://forum.knime.com/u/ipazin)\ **Post date:** [May 19, 2021, 9:23am UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/17 "2021-05-19T09:23:46Z")
Hello @sanderlenselink, you can also share workflows on [KNIME Hub](https://hub.knime.com/site/about). Additionally you can add file(s) into _data_ folder within workflow directory and use relative path(s) in order for workflow to be executed by others when downloaded. See here a bit more about it: [https://docs.knime.com/latest/analytics\_platform\_file\_handling\_guide/index.html#standard-fs](https://docs.knime.com/latest/analytics_platform_file_handling_guide/index.html#standard-fs) Br, Ivan ---
**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\ **Post date:** [November 17, 2021, 9:24pm UTC](https://forum.knime.com/t/dynamic-column-filtering/32729/18 "2021-11-17T21:24:19Z")
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.