ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

Maintaining consistency of data types when importing/exporting files (like SPSS and excel)

jmwagner
5 - Atom

I have an issue similar to this post:
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/SPSS-Output-Integers-as-Decimal/td-p/5...

 

My scenario:

I'm importing Excel data that has 2 fields (ID, PCT). The 'PCT' field is set as a Percentage data type in Excel. When importing it into Alteryx it sets it to a Double data type (Size 😎 which is fine. What I would like to have happen is to set it back to a data type similar to 'Percent' in Excel when being exported to an SPSS file. Is there a way to maintain consistency of data types when importing/exporting?

 

Example:

In Excel, we have a PCT as '35.3561286427735%' displayed as '35.4%' When exporting to excel it will have look like '35.3561286427735%' in SPSS. I was hoping to have it as '35.4%'

 

Also - the post I linked is another issue that I am running into. It will add decimals for when we don't need decimals. 

If I copy the data from Excel directly into SPSS it will have it as '35.4%'

 

DrewDavis
11 - Bolide

Hello, 

 

Could you attach a sample of your data. It appears that Alteryx is reading in the data correctly as Excel is likely just hiding the full data. The best way to approach this is to use the formula tool in Alteryx to round the numbers and then convert into a string. 

Hannah_Lissaman
11 - Bolide

Hi @jmwagner 

 

When Excel shows your percentage as 35.4%, it is rounding the value in the number formatting, not in the underlying value. As @DrewDavis rightly says, the original value with the full detail is still there in your Excel data. You can check this by changing the Excel values to the 'General' data type - you should see more decimal places.

 

I am not an SPSS expert, but I believe that you can copy across number formatting from Excel to SPSS, which is why it shows the same format in both programmes. 

 

However, Alteryx does not see the number formatting from Excel, and can only read the raw, underlying value. If you would like to round your percentages, you can use a formula tool with the Round function. However, take care. You will lose some precision when you do this, so I would recommend only doing this as a final step if you want to create a nicely formatted output. 

jmwagner
5 - Atom

I've attached some screenshots using dummy data. I will try to upload an example workbook as soon as I can. 

Hannah_Lissaman
11 - Bolide

Hi @jmwagner 

 

Thank you for the clarification and the screenshots. 

 

There is no simple solution here. Alteryx has no concept of a 'percentage' number format, or indeed of number formatting at all. It is working as expected, by reading the underlying value from Excel as a decimal with all available detail. It will only ever display and output values in their real form, and cannot apply a formatting layer. 

 

If you would like to change the appearance of your percentages in Alteryx, you have to edit the actual value. You can use the Round function to export a rounded value, but as noted you will lose precision. Similarly, if you would like to show 0.354 as 35.4, you would need to multiply by 100.

 

It is not possible to add a % symbol in Alteryx without converting to a String data format, which will mean that you data is imported to SPSS as text. 

 

It is possible to export the unformatted data and edit your number format once it is imported to SPSS using the syntax described here:

https://www.ibm.com/support/pages/i-would-display-percent-sign-numeric-variable-spss-data-view

Labels