Required output with $ sign on salary column

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

Workflow

Data Investigation

Accepted answers

All comments

binu_acs

@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

Screenshot 2024-01-09 223244.png

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

Screenshot 2024-01-09 223533.png

binu_acs

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

String_090123.yxmd

CoG

@Spy3y3

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

01-01-2023
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?

CoG

If those 2 date formats are the only formats, then you can just use the DateTime Tool (in Parse category) since it interprets "/" & "-" as effectively the same separator.

To answer your second question, it depends what the exact output your looking for is and what you have initially that you need to convert. The answer is probably, but more information is necessary to provide specifics.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703