Need solution regarding concatenate

[anonymous008_G]

Hi All,

Can I do concatenate for multiple column values to create unique one. For example as per below table I have concatenated first 3 columns and created new column in the excel. How would do same thing in Alteryx?

Codes	ROR_Period	TO_Date		Expected Output
BMSCH	1Y	12/31/2015		BMSCH1Y42369
OPSUY	1Y	12/31/2014		OPSUY1Y42004
MKHSL	1Y	12/31/2023		MKHSL1Y45291
AGSHY	1Y	12/31/2022		AGSHY1Y44926
KJAUS	1Y	12/31/2021		KJAUS1Y44561
BMSCH	1Y	12/31/2020		BMSCH1Y44196
OPSUY	1Y	12/31/2019		OPSUY1Y43830
MKHSL	1Y	12/31/2018		MKHSL1Y43465
AGSHY	1Y	12/31/2017		AGSHY1Y43100
KJAUS	1Y	12/31/2016		KJAUS1Y42735
BMSCH	1Y	12/31/2023		BMSCH1Y45291
OPSUY	2Y	12/31/2023		OPSUY2Y45291
MKHSL	3Y	12/31/2023		MKHSL3Y45291
AGSHY	4Y	12/31/2023		AGSHY4Y45291
KJAUS	5Y	12/31/2023		KJAUS5Y45291
BMSCH	10Y	12/31/2023		BMSCH10Y45291
OPSUY	1Y	12/31/2015		OPSUY1Y42369
MKHSL	1Y	12/31/2014		MKHSL1Y42004
AGSHY	1Y	12/31/2023		AGSHY1Y45291
KJAUS	1Y	12/31/2022		KJAUS1Y44926

[caltang · Accepted solution]

Like so?

[aatalai · Accepted solution]

@anonymous008_G use the formula tool with the following formula 

[codes]+[ROR_Period]+field to get those last numbers

How do you get those last numbers, then I would be able to help further

[caltang]

I see that you converted your date into a number, so you'll need this formula:

ToNumber(DATETIMEDIFF([TO_Date],'1900-01-01','days'))

The difference with '1900-01-01' is the numeric value of a date as per Excel. So, to concatenate them into a key, you will need to make sure all values are strings. You can use "ToString()" to achieve this.

Hope this helps!

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

[caltang]

I see you went slightly further back. Please change the formula like this:

ToNumber(DATETIMEDIFF([TO_Date],'1899-12-30','days'))

It appears you want the difference with 30th Dec 1899 instead.

[anonymous008_G]

Thank you both of you but this solution works for me
[codes]+[ROR_Period]+field

[caltang]

Hey @anonymous008_G - you marked @aatalai @‘a post as a solution even though the last bit doesn’t match your needs. 

The solution I provided is dynamic and gets you the exact same output as your expected output. I think you will need to reevaluate your pick. 

You can mark multiple solutions as accepted solutions - I would appreciate it if you can mark mine as well since mine ACTUALLY solves your problem and can help others with a similar problem solve it. 

No discredit to @aatalai - but that solution is just 75% of what you need/want. Kindly reconsider.

[caltang]

It will be a great disservice to the community in finding the best, most accurate, most robust, most dynamic, unique, and most importantly, best practice/correct way of getting what is needed to solve a similar problem by different users - so I hope this gives the context as to why I’m pushing for it.

[anonymous008_G]

I accepted that solution because the code was easy to implement and satisfied my needs. In sample data date got converted into number format after concatenating in excel. i just wanted to create unique string using multiple column concatenation. but yes your solution is dynamic and gives exact output as expected data. Thanks for your help.