How to Concatenate two columns in the order you want

[chefor99]

Hi
Please I would love to be able to concatenate two columns. in knime. I have tried using column combiner, aggregator, and many others, to no avail.
What I want is to append the " Date" column to the “Year and Month” such that the new column has dates in the format “Year-Month-Day”
Attached below is a sample
Thanks in advance
KNIME_project.knwf (8.5 KB)

[iperez]

Hi @chefor99

You can use the string manipulation node. You can use ther join or joinSep functions as you need. If the data type is integer you might also need the text function.

[takbb]

@chefor99 Unfortunately your excel file is external to your workflow, so it isn’t available to us. You might have this worked out now, but if you need further help, feel free to upload you excel spreadsheet separately in a new comment.

[takbb]

@chefor99 … There is often “more than one way to crack a nut!”, and often the approach you choose comes down to the specifics of your actual data, your level of experience, and sometimes just good old personal preference.

As @iperez mentioned, one approach to your problem is to use String Manipulation where, with a small amount of code, you might be able to bring your data together in the form you require. If your original data is in a format where concatenating columns with just a “-” would suit your needs, then the joinSep function will happily do that. (Thank you @iperez for that function. I had just been using just “+” to concatenate columns and “-” , and hadn’t noticed joinSep before! It is much cleaner!).

An alternative to String Manipulation is Column Expressions where you can also make use of joinSep or other functions to combine columns.

On the other hand, your particular data might not quite be suitable for straight concatenation. Maybe it needs to be modified slightly, in which case alternative functions may be needed. You can still possibly use String Manipulation or Column Expressions and in some cases this may be the best approach, but it might require some more complicated use of the various functions.

From what you said originally, you were headed in the right direction, but it seems that you needed a small nudge to show what to do next.

So I thought I’d put together a workflow (or workflows) that contain a variety of approaches which may be useful. Yours is a special case though, because we are talking about dates here, and so a lot of effort can be saved by making use of the “String to Date&Time” node along with the reverse “Date&Time to String”. In conjunction with another node that simply brings the data from your columns together in some intermediate form. A Column Aggregator or Column Combiner node (as you have already tried) will fulfil the intermediate requirement and then you can easily adapt your data without writing any functions. Using those two Date&Time nodes together you can quickly convert your date data that is held in one string into any other date format you like without having to use any functions or actual code. The only word of caution there is that the finer detail of some of the date masks can take a little getting used to.

I find the approach that suits me best with a tool such as KNIME is to firstly not expect any one node to be able to do the whole job for you, or even necessarily a big part of the job. Knime is all about breaking the job down into small steps. Add a node, look at the configuration, see what it can do, and ultimately run it and see what comes out. Does the output move you in the right direction? Is there some other node I can now use to move the output even further in the right direction? Sometimes you end up with a long series of nodes and maybe there is a better (shorter) sequence of nodes that can do the job. That happens to me, but that’s all part of the learning experience.

In the attached workflow, I have played with some sample data which may or may not be similar to yours. I have used the String Manipulation suggestion, along with the other nodes I have mentioned above to demonstrate a variety of ways that you could approach this problem. Take a look at the outputs of the different nodes to see how each of the approaches fulfil much the same requirements in different ways.

KNIME_combine_portions_of_date.knwf (68.8 KB)

[chefor99]

Thank you very much. This worked just fine for me