Joining dataset using expression in join condition

Q&A
, ,
1

Hi,
i am trying to join two tables together without using custom sql. My data source is using aws RDS.
this is the join condition i want to use in quicksight without using custom sql. Is it better to create a new column in the database or use a calculated field for example left({lgl_enty_pstl_cd}, 5) ? the reason i do not want to use custom sql is because its Harder to Maintain custom SQL, you lose the ability to use QuickSight’s drag-and-drop join interface, You can’t easily add new tables or fields without editing the SQL manually, not seeing all rows probably due to double aggregation when using table visual, If the underlying schema changes (e.g., column renamed), the custom SQL might break, and SPICE Limitations

LEFT JOIN schema.geo_zcta_natl_cdist_fact gzncf

        ON gsd.geo_st_geoid_cd = gzncf.geo_example_natl_cdist_st_geoid_cd

        AND gzncf.geo_example_natl_cdist_st_geoid_cd= LEFT(lemdf.lgl_enty_pstl_cd, 5)
2

Hi @izzycs,
Regarding your question around creating a new column or using calculated field, the best option will be a bit dependent on how you utilize and what would be more important to you. While both could be viable options, the biggest difference could stem from loading/refresh times when utilizing.
If you were to create a new column in your database, you’ll be handling a bulk of the calculation at the database layer. So this could be more valuable if you’re planning to utilize this join condition in various areas or across multiple joins, I would suggest added a new column to the database.
If you were to use a calculated field, these would be run on the refresh timeframe, potentially leading to a longer ingestion time. This could be valuable though as it creates an easier way to adjust in the future if you ever need to make changes to the syntax.

Let me know if this helps or if you have any additional questions

3

Hi @izzycs,

Following up as it’s been awhile since we last heard from you on this thread; did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

4

Hi @izzycs,

Since we have not heard back further, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this topic for any relevant information if needed.

Thank you