I have two datasets in QuickSight: one from Redshift and one from Athena(both are using SPICE). I edited the Athena dataset and clicked on “Add data” to join the Redshift table. I need all the values from the Athena table and only the matching values from the Redshift table, so I performed a left join on a common column.
The Athena table contains all the columns I need, and from the Redshift table, I require latitude and longitude. Before joining the datasets, I created a map visual using the Redshift table’s lat and long data, and it worked as expected. However, after joining, saving, and publishing the dataset, and then creating a visual with lat and long, the values appear different.
For example, the original table has latitude values like 29.3456 and longitude values like -95.3456, but after the join, I see latitude values such as 0.02934 and longitude values like -0.0953456. I checked the data type after the join on the dataset page, and it appeared correct, but once I saved and published, the values were off.
Is there a way to correct this, or do i need to change my approach?
Indeed this seems odd. If the original redshift dataset has the correct lat/long, and its affected by join, this likely should be submitted as a support ticket.
Most likely it has something to do with how that field is coming in from redshift - i would recommend casting it as float in customSQL prior to bringing into QS.
An alternate quick fix is to multiply by 1000 in a calc field.
Thank you for your reply! I will definitely submit a ticket.
Also, I don’t see any issues with the Redshift table—it is working fine (used CAST as well), and I can see the data points exactly as expected.
However, something is happening with the join itself. We cannot create a calculated field using a geospatial field on the dataset page, as it results in an error stating that geospatial fields cannot be used in calculations.
I am able to create a calculated field in the analysis after the join, but I cannot use that field in the map visual, which is the main problem.
I had faced a similar issue. In my case the dataset did have joins; but the map visual was working fine and starting November 2024 the longitude and latitude changed to decimals though we did not have any changes in the dataset.
It has manifested itself in a different way in your dataset. I checked my case and what I see is that I created two calculated fields to multiply the original longitude and latitude columns by 1000 and added them as the Geospatial fields in the dataset.
What is the issue you face in adding the calculated fields in the dataset?
Thank you for the response. So, it says “Geospatial fields cannot be added in calculated fields”
I tried as you said - convert to decimal and then create calculated field and then convert to lat/long. I tried latitude * 1000 but it gives values like 24567.0 on the data preparation page.
Also, the original values look good in the dataset page but they are rounding up some how like this in analysis (attached image).
Hi @shravya,
It’s been awhile since last communication took place on this thread, were you able to raise a ticket and get a helpful solution for your case or did you have any additional questions?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Hi @Brett - No, I have altered my requirements to a single dataset for now as it was a bit urgent. However, I will raise this issue separately, find a solution, and post it accordingly. You can close this topic.
