If contains in a string

[matejek88]

hi guys,

I am struggling with a fairly simple task in knime. I have a column with the data like this:

List
01_01_01|01_01_02|01_01_03|01_03_01|01_03_02|01_03_03|01_03_05|01_04_01|01_04_02

Another column in the same dataset has the following column:

Position
01_01_03

All I want to do is a simple formula where if List contains Position then returns a boolean or the value.
It might be me but Rule engine doesn’t seem to be able to do string to string match.
If wrong, please correct or help me.

Thanks a lot!
Matt

[armingrudd]

Hi @matejek88,

In this case, you can use the String Manipulation node with this expression:

regexMatcher($List$, join(".*", $Position$, ".*"))

[matejek88]

Thanks @armingrudd

and how would you do it if Position is in another datatable? Basically, I want to join 2 tables on 2 levels of matching criterias.

1st level: $ID and ID$ - I can have here a 1on1 relation.
2nd level: $Position$ is contained in the $List$

Appreciate your time!

[armingrudd]

Isn’t it possible for you to first join the tables based on IDs and then do the “position” matching?

Joiner

[tqAkshay95]

Hello @matejek88,

Yes, you need to first join two tables, and then you can achieve the desired result.

For your reference, please check the attached images.

[matejek88]

@tqAkshay95 Thanks, for the help but I actually came up with another solution becuase the dataset required to change the previous one. @armingrudd

So where I got stuck in that solution is a looping.
How can I dinamically join the columns as follows? The $List$ can have 1-30 positions and I need the workflow to handle that.

$Type$+$List_Arr[0]$
$Type$+$List_Arr[1]$
$Type$+$List_Arr[2]$
$Type$+$List_Arr[3]$
$Type$+$List_Arr[4]$
$Type$+$List_Arr[5]$
$Type$+$List_Arr[6]$
$Type$+$List_Arr[7]$
$Type$+$List_Arr[8]$

After that I will be able to just a simple join on concataned fields which is more reliable.
Thanks a lot!

loops 1.knwf (7.6 KB)

[tqAkshay95]

Hello @matejek88,

Checked Your Workflow.

Do you want to compare the “Position” with each column?

[matejek88]

The $Position$ column can be disregarded, there will be more data coming for procession but I am capable of handling that. Only the dynamic joining causing me headache…thanks!!

[tqAkshay95]

Hello @matejek88,

I understand what you’re looking for.

Following attached workflow, and it seems to be working well for restructuring the data.

restructure data.knwf (26.1 KB)

From this workflow, I obtained results like:

[matejek88]

Unfortunately I cannot run it because ‘Row to Column Names’ node is not available for me is there any way to use another node instead?

Really appreciate it!

[tqAkshay95]

Hello @matejek88,

You can directly drag and drop the node using the following link:

Row to Column Names

[matejek88]

That is the problem is that I am not allowed to do that. I think my company has blocked such option.

[tqAkshay95]

@matejek88, you can directly use the ‘Column Rename’ node and then apply the ‘Row Filter’ node as shown below:

restructure data1.knwf (29.2 KB)

[matejek88]

sorry but…I also dont have column rename node…lol
but I also dont think it will be dynamic at that point if I rename the columns manually.

[tqAkshay95]

@matejek88

When you visit the following link:

Row to Column Names

you will first need to sign in.

[matejek88]

did it already and still…

[armingrudd]

@matejek88,

Which KNIME AP version are you using?

Row to Column Header (4.7) or Row to Column Names (since 5.x) is not available in 4.6.
Isn’t it possible for you to install the most recent version (5.2.1)?

KNIME Analytics Platform 5.2.1 is live

Hello Everyone, As with every major release, we’ve performed an additional round of QA and now recommend KNIME Analytics Platform 5.2.1 for use in production environments. All relevant resources for KNIME Analytics Platform, including download files, are available on our product download pages. Take a look at the release notes to learn more about the security-related changes to various visualization nodes that could impact existing workflows. KNIME 5.2 is a major new release featuring a signi…

[matejek88]

sadly not, this is the version that the company allows.

@armingrudd can you maybe help? I would like to have the dinamically joined result as shown in the picture. (Please disregard the Pisition column in the final result and the header names in the picture below)

I am trying looping because that seems like the quickest solution but gives me headache already…

[armingrudd]

Looks like you are looking for the Unpivoting node (“Unpivot” since 5.x).

After the Cell Splitter in your workflow, add the Unpivoting node, include array items in the “Value columns” and the “Type” column in the “Retained columns”:

Is that what you were looking for?

[matejek88]

yesss, geez, I have felt it that is too easy…after I did some string manipulation and pivoting back the table and it worked…

Thanks a million for both of you. @armingrudd @tqAkshay95