Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

General Discussions has some can't miss conversations going on right now! From conversations about automation to sharing your favorite Alteryx memes, there's something for everyone. Make it part of your community routine!

SOLVED

How to Create a unique column In-DB

davidlhowes
6 - Meteoroid

In order to join two tables I need to create a unique column which will be common.

The columns which I would like to combine are different formats ("Client_No" = V_WSTRING and "Entry_No" = Int32)

I tried using the Formula in-DB tool to create a new column: Unique = "Client_No"+"Entry_No", this normally works to create a concatenation of the two fields.

I receive an error that it cannot convert the nvarchar value to data type int. I then tried to do the combination in stages to create first of all a column of type V_WSTRING that was a text version "Entry_No", thinking that I could then combine this new field to "Client_No".

This was unsuccessful as the new column remained as an INT32 despite specifying the type to be V_WSTRING, I can only presume it inherited the type from the source information.

 

Thanks for any input.

David

MarqueeCrew
19 - Altair
19 - Altair

Depending on your database, you should GOOGLE and find the appropriate CAST or To_String type of function that works in your SQL.  Then you can implement that expression.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Thableaus
17 - Castor
17 - Castor

Hi @davidlhowes 

 

Provide the database you're using and we'll be glad to find an appropriate function.

 

As @MarqueeCrew said, In-DB Formula Tool is dependent on the database syntax. 

 

Cheers,

davidlhowes
6 - Meteoroid

Hi Thableaus,

It is a Microsoft SQL Server database.

 

Thanks,

David

MarqueeCrew
19 - Altair
19 - Altair
CONVERT(varchar(10), Entry_No)
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Thableaus
17 - Castor
17 - Castor

@davidlhowes 

 

Adding up to @MarqueeCrew's answer to your topic:

 

CONCAT (Client_No, CONVERT(varchar(10), Entry_No))

 

This should work for creating your Unique Column in SQL Server.

 

Cheers,

davidlhowes
6 - Meteoroid

Thank you all for your help

Labels