# How to avoid duplicates when joining Fact and Mapping tables in QS

**URL:** <https://community.amazonquick.com/t/how-to-avoid-duplicates-when-joining-fact-and-mapping-tables-in-qs/49763>\
**Category:** Q&A\
**Tags:** dashboard, formulas, data-source, calculations, analysis, dataset, formatting, Business-Intelligence-Engineer, quick-sight\
**Created:** [September 18, 2025, 10:26pm UTC](https://community.amazonquick.com/t/how-to-avoid-duplicates-when-joining-fact-and-mapping-tables-in-qs/49763 "2025-09-18T22:26:37Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Prav](https://avatars.discourse-cdn.com/v4/letter/p/a587f6/32.png) [@Prav](https://community.amazonquick.com/u/Prav)\
**Post date:** [September 18, 2025, 10:26pm UTC](https://community.amazonquick.com/t/how-to-avoid-duplicates-when-joining-fact-and-mapping-tables-in-qs/49763/1 "2025-09-18T22:26:38Z")

</div>

Hi All,

I’m facing an issue with duplicate records when joining a Fact table to a Channel mapping table in Quick Sight.

#### **Sample Data:**

**Fact Table (Fact\_Sales):z**

| | B | C | D | E | F | G | H | I |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Business | Version | Year | Company | Channel | Brand | Group | Month | Value |
| Biz1 | V1 | 2025 | C1 | ChX | Br1 | G1 | Jan | 100 |
| Biz1 | V1 | 2025 | C1 | ChX | Br1 | G1 | Feb | 200 |
| Biz1 | V1 | 2025 | C1 | ChY | Br2 | G2 | Jan | 50 |

| |
| --- |
| |

Channel Mapping (Channel\_Map):

| | B | C |
| --- | --- | --- |
| Channel Plan | Channel Group | Channel |
| PlanA | Retail | ChX |
| PlanB | Corporate | ChY |
| PlanB | Employee | ChY |

| |
| --- |
| |

Brand Mapping (Brand\_Map):

| | B | C | D |
| --- | --- | --- | --- |
| Brand | Series | Code | Description |
| Br1 | S1 | G1 | Product A |
| Br2 | S3 | G2 | Product B |

### **Problem:**

Common Column Channel

When I join the Fact table with the Channel mapping on Channel, rows with ChY get duplicated because in the mapping table ChY belongs to both Corporate and Employee groups.

So instead of Value = 50, I end up with Value = 100.

### Constraints:

- I cannot make changes to the source files.

- I can only work with Quick Sight dataset joins and calculated fields inside SPICE or Analysis.

What I’ve tried so far:

- Created calculated fields to normalize keys (e.g. trim/upper) → joins work but still duplicate values.

- Tried dividing values by a count of duplicates → works sometimes, but breaks in some groups.

- Looked into filters at dataset level → but that removes some valid mappings.

Question:

What’s the best practice in Quick Sight to handle this kind of one-to-many mapping without changing the source files?

- Should I solve this in the dataset join itself (if so, how)?

- Or is there a recommended way with calculated fields to avoid double-counting?

---

<div class="post-metadata">

**Author:** ![andrepgn](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/andrepgn/32/25909_2.png) [@andrepgn](https://community.amazonquick.com/u/andrepgn)\
**Post date:** [September 19, 2025, 8:09am UTC](https://community.amazonquick.com/t/how-to-avoid-duplicates-when-joining-fact-and-mapping-tables-in-qs/49763/2 "2025-09-19T08:09:31Z")

</div>

Hi @Prav ,

you can have different approaches here I think.

First one can be change the way you retrieve the data. Are those data in a DB that you can query?  
If so, you can create a custom query, removing the duplicated lines BEFORE getting the data in Quick Sight.

**Approach 1** : eliminate the field Channel\_Group (this assumes that this field is the only one that can have different values in the rows you want to merge)

```auto
SELECT DISTINCT f.*, cm.Channel_Plan
FROM Fact_Sales f 
LEFT JOIN Channel_Map cm ON f.Channel = cm.Channel

```

This will result in something like this.

| Business | Version | Year | Company | Channel | Brand | Group | Month | Value | Channel\_Plan |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Biz1 | V1 | 2025 | C1 | ChX | Br1 | G1 | Jan | 100 | PlanA |
| Biz1 | V1 | 2025 | C1 | ChX | Br1 | G1 | Feb | 200 | PlanA |
| Biz1 | V1 | 2025 | C1 | ChY | Br2 | G2 | Jan | 50 | PlanB |

**Approach 2** : aggregate the Channel\_Group field in a single field (same assumption as before regarding the changing values of the fields)

```auto
SELECT 
    f.*,
    cm.Channel_Plan,
    ARRAY_JOIN(ARRAY_AGG(DISTINCT cm.Channel_Group), '/') as Channel_Groups
FROM 
    Fact_Sales f
    LEFT JOIN Channel_Map cm ON f.Channel = cm.Channel
GROUP BY 
    f.Business, 
    f.Version, 
    f.Year, 
    f.Company, 
    f.Channel, 
    f.Brand, 
    f.Groups, 
    f.Month, 
    f.Value,
    cm.Channel_Plan

```

This will result in something like this:

| Business | Version | Year | Company | Channel | Brand | Group | Month | Value | Channel\_Plan | Channel\_Groups |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Biz1 | V1 | 2025 | C1 | ChX | Br1 | G1 | Jan | 100 | PlanA | Retail |
| Biz1 | V1 | 2025 | C1 | ChX | Br1 | G1 | Feb | 200 | PlanA | Retail |
| Biz1 | V1 | 2025 | C1 | ChY | Br2 | G2 | Jan | 50 | PlanB | Employee/Corporate |

**Approach 3** : eliminating the doubles in Quick Sight.  
You can do this by, e.g., selecting all the other fields and not the Channel\_Group one, and then using an Average aggregation on the Value field.

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/c/0/c039d76ab02bb9fa9febda3708e7b9e0d221f496.png)

Let me know if some of these might help!  
Andrea

---

<div class="post-metadata">

**Author:** ![Prav](https://avatars.discourse-cdn.com/v4/letter/p/a587f6/32.png) [@Prav](https://community.amazonquick.com/u/Prav)\
**Post date:** [September 19, 2025, 10:51am UTC](https://community.amazonquick.com/t/how-to-avoid-duplicates-when-joining-fact-and-mapping-tables-in-qs/49763/3 "2025-09-19T10:51:31Z")

</div>

Hi Andrea,

Thanks a lot for the detailed explanation and examples — very helpful!

I do have a follow-up: in my case, I must keep Channel Group in the output since the business requires reporting broken down by Retail, Employee, Corporate, etc. Because of that, Approach 3 (dropping Channel Group and averaging the values) doesn’t work for me.

Unfortunately, I can’t modify the source files (the data comes in as CSVs), so I don’t have the option of running SQL to apply DISTINCT or ARRAY\_JOIN as in Approaches 1 and 2.

👉 Is there any way in Quick Sight itself (either in the dataset prep layer or with calculated fields) to achieve something similar to Approaches 1 or 2 — basically deduplicating the join so each Fact row only maps once, while still keeping Channel Group available for reporting?

Thanks again for your help!

---

<div class="post-metadata">

**Author:** ![Prav](https://avatars.discourse-cdn.com/v4/letter/p/a587f6/32.png) [@Prav](https://community.amazonquick.com/u/Prav)\
**Post date:** [September 22, 2025, 9:21am UTC](https://community.amazonquick.com/t/how-to-avoid-duplicates-when-joining-fact-and-mapping-tables-in-qs/49763/4 "2025-09-22T09:21:45Z")

</div>

The issue is resolved. Made a work around in the Spice layer on one of the Datasets..

Thanks

Praveen

---

<div class="post-metadata">

**Author:** ![andrepgn](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/andrepgn/32/25909_2.png) [@andrepgn](https://community.amazonquick.com/u/andrepgn)\
**Post date:** [September 22, 2025, 9:49am UTC](https://community.amazonquick.com/t/how-to-avoid-duplicates-when-joining-fact-and-mapping-tables-in-qs/49763/5 "2025-09-22T09:49:27Z")

</div>

Hi @Prav,  
can you sorry for being late, but happy to see you solved the issue! 😃  
Can you explain what you’ve done for future reference to the Community?

Andrea