Skip to main content

Hello all

A basic (and rookie) question, but I couldn’t find the answer in the manual or tutorials, or on this forum.

After I’ve loaded source data into Ataccama ONE Desktop, I’m looking to create a new column and fill it with the concatenated content of some other columns (two or more), maybe with a bit of text in between. In Excel I’d use the Concatenate function...

Example from Excel

As you can see, I can then lookup information based on Column D and use that for bringing in various lookup values (the real use case is not name-based, this is just to create a simple example)

Note - I’m not looking to merge different tables (with join, union, or representative creator - unless those tools can do what I’m looking for) - this is simply creating a new column/field which is a concatenation of two or more other fields

Context: I’m building a lookup based on multiple attributes - I’ll combine them into a single attribute to give me a single lookup key and then I can use that for various other purposes in my plans

I was expecting a ‘concatenate’ function to be reasonably common but I’ve only seen it used between different fields in the Representative Creator - is there not a way to do this within Column Assigner?

This looks like reverse of the pattern parser but I couldn’t find a way to make that run backwards!

thanks in advance

Nigel

Hi @nigel!

So in Ataccama, you can use the plus “+” sign to concatenate either strings (using the simple quotes) or values in the columns.

Example:

‘Test project ’ is a string and w_itemName is a column that can contain some other string.

In your case, it would be: 

Input1 + ‘ ‘ + Input2 + ‘ from ‘ + Input3

We also have concatenate function, but that can be used only in grouping steps to concatenate values from a group.

I can also  recommend documentation inside the ONE Desktop, under Help → Help Contents. And if you search for Expressions, there is a complete documentation offering the list of functions, explanation of the syntax etc.

Let me know if this helps.

Kind regards,

Anna


That’s perfect Anna, thanks - I tried it in Column Assigner and it worked perfectly

Small differences from Excel (single quote for adding fixed text, using + instead of & for concatenating columns) - but I expect users will become familiar with this very quickly

Also thanks for pointing out where to find this in the help text - it will help me use in the inbuilt help and search functions more easily for future questions 

Nigel


HI,

How does this treat nulls?  If one of the fields is null in SQL the whole expression returns null but this doesn’t appear to be the case in ataccama expressions.  Can you confirm? or is there a way to use  nulls in the manner I described above.

 

thanks!

 

Greg


Hi @greglvaughan , sorry for the late reply, I didn’t notice your comment.

Indeed, the null in one of the expressions won’t cause the whole expression to be nulled. Is that what you would prefer? I have to think of a way how to do that though as I’ve never actually needed it.

Kind regards,

Anna


Hi Anna,

 

No worries I worked it out at the database level.  Thanks for getting back to me.

 

Have a great day!

 

gV


Hello @greglvaughan , nice to hear that!

Just for reference for other users in the future, looks like even every DB system has it differently, looks like in Ataccama it should work similarly to Oracle: https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Concatenation-Operator.html when null can be a result only if all operands are nulls. We don’t have any elegant way or function to go around that, only using some iif statements to check for nulls in every operand or by creating some function in the expression.

Kind regards,

Anna


Reply