Transform column headers/multiple rows

[TobiP]

Hi!

please see the following screenshot for reference, “Current table” is what I currently have, “New table” is what I would like KNIME to do.

It should do the following:

• Remove the random Text (the text will always be a string called “random Text”) in Cell F1.
• Fill up the missing/empty headers in cells C2 and E2 with the string/header from the adjacent cells to the left (B2 and D2) and add a “(2)” or comparable indicator.
• Fill up the missing/empty headers in cells G2 and H2 with the string/header from the adjacent cells above (G1 and H1).

The “Current Table” to copy/paste into excel:

					random Text	HeaderA	HeaderB
HeaderC	HeaderD		HeaderE		HeaderF		
Value	Value	Value	Value	Value	Value	Value	Value
Value	Value	Value	Value	Value	Value	Value	Value
Value	Value	Value	Value	Value	Value	Value	Value
Value	Value	Value	Value	Value	Value	Value	Value
Value	Value	Value	Value	Value	Value	Value	Value
Value	Value	Value	Value	Value	Value	Value	Value

I did already use Google to find answers but alot of them point to a “Column expressions” node, which unfortunately I cannot find in my repository? (KNIME 4.6.3)

Thanks in advance!

[iCFO]

I would use the excel reader 2 times (adjusting the 1st line to target both tables) then do a header replace on table 1 with table 2.

If need help then you can upload your sample excel file and we can do a workflow for you.

You can use this component from @takbb

Replace Table Column Names – takbb

Or my interactive view based component if you want some advanced mapping capabilities as well. (You can transform and then use the “pre-map” to populate headers as a starting point)

Header Re-Map – icfo

[TobiP]

Hi,

thanks for your reply. Works for me!

Regards

[Daniel_Weikert]

@iCFO and the “skull” represents what exactly

[iCFO]

Playing up the “Head”, in Header and I thought the X-ray worked with the mapping concept… Hard to come up with a cool icon for term Column Name!

Now that I think about it, I was working on a project for a client in the deathcare industry when I built it as well!

[Daniel_Weikert]

@iCFO ah ok,
i first thought something like “use it if you like to crash your workflow”
So you are a freelance BI Consultant / Database Developper or something like this. Interesting client base you have
br

[iCFO]

I have evolved into kind of a multi-tool for bringing financial management sophistication to small and medium sized businesses, so I wear a lot of hats… Essentially I am a tech heavy Fractional CFO (or CFO support) and Management Consultant that brings a lot of additional skills to the table. I create a data driven management systems, interactive reporting and budgeting systems, streamline and automate processes, create custom software solutions, work closely with the Board and CEO on strategic planning and policies, design merchandising solutions, help with marketing and advertising strategy, etc.

On any given day I could start off working in KNIME / BI tools in the morning, re-write the management policies for a non-profit mid-day, do some visual design / audio / video editing work in the afternoon, and be creating a custom merchandising web app at night.