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? CodesROR_PeriodTO_Date Expected OutputBMSCH1Y12/31/2015 BMSCH1Y42369OPSUY1Y12/31/2014 OPSUY1Y42004MKHSL1Y12/31/2023 MKHSL1Y45291AGSHY1Y12/31/2022 AGSHY1Y44926KJAUS1Y12/31/2021 KJAUS1Y44561BMSCH1Y12/31/2020 BMSCH1Y44196OPSUY1Y12/31/2019 OPSUY1Y43830MKHSL1Y12/31/2018 MKHSL1Y43465AGSHY1Y12/31/2017 AGSHY1Y43100KJAUS1Y12/31/2016 KJAUS1Y42735BMSCH1Y12/31/2023 BMSCH1Y45291OPSUY2Y12/31/2023 OPSUY2Y45291MKHSL3Y12/31/2023 MKHSL3Y45291AGSHY4Y12/31/2023 AGSHY4Y45291KJAUS5Y12/31/2023 KJAUS5Y45291BMSCH10Y12/31/2023 BMSCH10Y45291OPSUY1Y12/31/2015 OPSUY1Y42369MKHSL1Y12/31/2014 MKHSL1Y42004AGSHY1Y12/31/2023 AGSHY1Y45291KJAUS1Y12/31/2022 KJAUS1Y44926

Need solution regarding concatenate

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

Help

Accepted answers

caltang

Like so?

For anon.yxmd

aatalai

@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 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.

All comments

caltang

Like so?

For anon.yxmd

aatalai

@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.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335