Hello, I have some questions regarding data being duplicated when I try to join tables using Quicksight’s features.
I am trying to join the two tables (1. Product Inventory List, 2. Product’s expiration date lists) using the joint clause: SKU or product name.
However, since one of the tables are having multiple lines for each SKU as some products have multiple expiration dates, the joined datasets are giving me much more numbers for each product than expected.
I tried using answers from other questions that were answered in the community (sum(inventory) / count(inventory)), but it gives me very small numbers around 1 for every product.
Is there a feature that helps remove duplicate values for my desired value? If not, is there any function for the calculated field that I can use?
Thank you for your support.
I think you are going to need to use a SQL with clause on your 2nd table. It needs to return one row per SKU.
with product_exp as
(select sku, max(exp_date) from product_expire_table
group by sku
)
then join this to your product inventory table.
This assume that exp_date is a required field. if it isn’t, you’ll need something like this:
with product_exp
(select sku, max(coalesce(exp_date,‘2099-12-31’)) from product_expire_table
group by sku
)
That worked perfectly! Thank you for your help 