Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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

6 REPLIES 6
MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
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
20 - Arcturus
20 - Arcturus
CONVERT(varchar(10), Entry_No)
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
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