Required output with $ sign on salary column

[Spy3y3]

Good Morning, Afternoon, Evening, as your respected time,

I am connecting today with one help in Alteryx, I hope I will get it.

I am working on the project and there is one column named annual salary mentioned in the $ sign with we underscore string data type I used the select tool to try to change the data type into double but got the output as null.

Question: What is the average annual salary for each department in the dataset?

Require Output: With $ sign if possible

I would like to mention that I used the below-mentioned tools,

Input - Data Cleasing - Select - Summarize

Image 1:

Image 2:

Image 3:

 

Note: In the first image, I also removed the trailing whitespace

[binuacs]

@Spy3y3 The annual salary field is string data type, you are trying to change string data type to double which results the null value. if you want to keep the $ in your annual salary field keel the data type as string

[CoG]

In the third image you provided, no data cleansing tool is present, but this is a necessary step (or you can use the Formula Tool). You need to remove all punctuation before attempting to convert, otherwise, as far as Alteryx is concerned, you just asked it to convert "Table" to type Double. It can't handle that and outputs null instead.

Solution:

1. Remove all punctuation from Salary Column via Data Cleansing or Formula Tool (If any salaries have cents e.g. $100,000.05 then you have to use the Formula Tool, if not then Data Cleansing will work fine)

2. Use Select Tool to convert column to type Double (or int32)

3. Perform desired Aggregation with Summarize Tool

4. Use the Formula Tool to add new column of string type and use the formula:

   "$" + ToString([Annual Salary],2,",")

That's it! Ultimately, you just need to jump back and forth with the data types.

[cjaneczko]

If you are outputting this to Excel, you can apply the "$" in excel by formatting that column. In your Output tool, you can check the "Preserve Formatting" checkbox and the dollar sign will always be there. Otherwise if you arent outputting this to excell you'll want to keep the field as String which was mentioned above.

[Spy3y3]

But still getting same error

[Spy3y3]

Thank you so much Andrew for your quick response.

Image: 1 Int32, Int64 and Double is not working

 

Image 2: V_WString working but the avg option is not available

 

[binuacs]

@Spy3y3 you cannot apply numerical functions in the string data type. Attaching a sample workflow 

[CoG]

@Spy3y3 

As @binuacs also already said, the comma itself is also a string character that needs to be removed.

Also, you do not need both Data Cleansing and a Formula Tool. But whichever you choose, the output should be nothing but digits before you convert to Numerical type (int vs double) via Select Tool to perform aggregation.

[Spy3y3]

Hi,

Thank you for sharing the detailed workflow. I just wanted to know how to change the date data type. I have dates in Excel, which I retrieved using the input tool in Alteryx, and they have different formats in one column.

For your reference, I have mentioned below the examples.

1. 01-01-2023
2. 01/01/2023

[Spy3y3]

Hi  Andrew,

Thank you for your suggestion. Just wanted to know Suppose, If we didn't get the exact output as per our requirement then is there any way that we can write any formula to get the output?