Joining two tables using calculated field

Question & Answer
,
1

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?

2

You can only join datasets on columns from the datasets. You can’t add a condition on the join in quicksight.

So you will need to make a calculated field in the excel file dataset that allows for leading zeros and join your redshift dataset onto it.

This might look like this

ifelse(strlen(toString({int bearable}))=4,concat('0',toString({int bearable})),toString({int bearable}))

That would be for a length of 4. You could continue till a length of 1 if you wanted to with more if statements.

Then you would join your datasets on this new field.

1 Like
3

Hi @jp207 - Is it possible to follow the below steps, possible a solution as well.

  1. 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.
  2. 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.

Regards - Sanjeeb

1 Like
4

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.

5

Ahh I mispoke. You can’t join on calculated fields. You would need to do what @Sanjeeb2022 suggested.

1 Like