I understand that calculated field columns can’t be used to join two tables. I have one dataset from an Excel file with ids and other information. The problem is that this file doesn’t have a leading zero for ids that have less than 5 characters (7001 vs 07001). The other data comes from a Redshift source and it has the leading zero. In order to correct the Excel formatting, I have to create a calculated field. Unfortunately, this calculated field does not show up when I use the QuickSight join function.
I think custom SQL can be used to merge/join the two datasets, but I’m not sure how to do this? How do I SELECT the dataset from different sources. I have pretty limited SQL experience. I’ve mostly used it to query data from the company’s Redshift data source. I don’t know the SQL query I’d write to join the two datasets using the calculated field from one of them. Any examples I could take look at?
Hi @jp207 - Is it possible to follow the below steps, possible a solution as well.
Create a data set from Custom SQL for redshift table and add new column with trim the 0. The new column will be populated at data set level as a derived column.
Use the derive column for redshift and excel column in join.
This way you really not require any calculated field also. Please give a try and post the outcome.
I’m a bit confused. I did make a calculated field similar to what you are suggesting. The problem is that this calculated field column does not show up as an option for joining.