Searching for column names in the data from another column

[Startide]

Hi gang,

Hopefully my title makes sense…

I have a node where I got all the items I want to list as columns in another table :

dcsapp.desc
dcsapp.url
dcsapp.m_ssmenu
dcsapp.m_ssnom
dcsapp.m_menu

I want to use them in another table as new columns headers, and search for those column names in another column named DATA (that contains a bunch of CSV data).

Basically :

DATA	dcsapp.url	dcsapp.m_ssmenu	dcsapp.m_ssnom
dcsapp.url;dcsapp.m_ssmenu	dcsapp.url	dcsapp.m_ssmenu
dcsapp.m_ssmenu		dcsapp.m_ssmenu
dcsapp.m_ssnom			dcsapp.m_ssnom

I managed to generate the list, but putting it as column headers is giving me trouble, also how to search for the content of the column name in another column ?

[rfeigel]

Could you post your data and explain “and search for those column names in another column named DATA (that contains a bunch of CSV data).” further?

[Startide]

What I mean is that I grabbed all the data contained in “data” and turned it into a list of unique items :

dcsapp.desc
dcsapp.url
dcsapp.m_ssmenu
dcsapp.m_ssnom
dcsapp.m_menu

I want to create extra columns in my data table, based on those values.

After that I want that for each of those columns I check for each line if the column name exists in the column data. Then display it (or 1, or X… whatever just something that shows that this particular label is indeed contained in the data column.

Hope I make more sense.

[Startide]

To explain this further : I have managed to isolate the names I want for my columns (middle node with the output port opened).

And counting them and setting it as a flow variable (right port).

Now I want to add 7 columns (in this case, but could be 5, 19 or 50) named after the rows contained in the first port opened (dcspref thing).

Ideas ?

[rfeigel]

Its really difficult to try to help you without your current workflow including your data. Have you tried to transpose the column with the desired column names? This will create a row with the column names which can be converted into headers with the Row to Column Names node.

[Startide]

Thanks a bunch, sorry for lack of details. Row to column names was clearly what I needed !!!

Now is there a way to search for the column name inside another column ?

Basically I want to search for dscpref_ces: dcsapp.url (and the others) in another column and display something when it matches (see the table a few comments up).

Is that even possible ? That’s pretty niche and my columns can vary in numbers of course… otherwise where would be the fun

For example here I should to find “dcspref_ces: DCSApp.Desc” and dcspref_ces: DCSApp.URL :

description: Pref subtool;cn: preferences;dcspref_ces: DCSApp.Desc*$*String-$-Administration;dcspref_ces: DCSApp.URL*$*String-$-https://dcs.XXXXX.com/rnz_tls2/;

[gonhaddock]

Hello @Startide

@Startide

Basically I want to search for dscpref_ces: dcsapp.url (and the others) in another column and display something when it matches (see the table a few comments up).

What you are describing here is a wildcard validation exercise. Please check the following topic for some insights:

Filter rows using list of words with wildcards

Hi, I have a file with about 2M rows and several columns. 1 column contains the description of items: e.g. “T-shirt made from recycled cotton”, or “laptop computer year 2019” or “salad bowl made from glass and iron”. I want to keep only rows that are of interest to me, so I created a list of “words to remove” and I want to remove all rows that contain 1 or more of these words. The item descriptions are very long and can differ slightly so I used “table creator” nodes where each row contains w…

BR

[Startide]

I am not sure it’''s the same, or maybe I misunderstood ?

I want to check for each of those column names inside another column and write if that data does match. See the table in my first message, hopefully that’s easier to understand than my broken english

[rfeigel]

As I’ve said several times, it would be a lot easier to help you if you would provide some sample data.

[Startide]

Will do when I get a bit of free time, I need to upload a workflow file that’s right ?

[rfeigel]

Make sure the data is included in the workflow. If you’ve got any readers pointed to local files, they won’t be included in your post. Create a “data” folder inside your workflow and store your data there. Then point any readers as shown below:

[Startide]

searching for columns headers.knwf (78.4 KB)

I hope that’s the correct way to share a workflow

I put the data to be searched in a table creator
I put the column headers that I want to search for in another header.

I managed to create the table with my list as column headers, now I want to search each of those header string into the data table.

[Startide]

Ok I made progress

I am looping on my list of values I want to search for I create two string manipulation variables :

One for the search pattern
One for the final column name

I use rule engine to output to a new column if there is a match or not for the current search pattern, then rename that column in the last node.

Now, how do I complete that loop ?

Currently I only get the last loop column as a result, so maybe I am still doing this wrong…

[gonhaddock]

Hello @Startide
Thank you for sharing the data. Take a look in to the following workflow.

20250409_searching_columns_headers_v2.knwf (25.2 KB)

Please share your thoughts.
BR

[Startide]

Thanks, I got that part ok now. On to populating columns when looping ^^

[Startide]

Ok I figured it out, but it’s weird : when looping for the data it actually doesn’t overwrite the column I add, but it iterates on ALL the columns. So I end up with a bunch of columns labelled "iter #1, #2 and so on…

I used a column filter to remove all ITER columns, but I am curious as to how to prevent this from happening to begin with

[rfeigel]

It would be a lot easier to try to help if you would share your workflow. Without the workflow we can only guess about what’s going on.

[Startide]

My apologies, I had to cleanup the data so it doesn’t contain anything that shouldn’t be on the internet

cleaning up data(example).knwf (147.4 KB)