
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
duplicate values by using of left join
Hi Folks,
i have two table:
Category | Product | Sales |
C1 | p1 | 10 |
C1 | p1 | 50 |
C2 | p2 | 20 |
C2 | p3 | 12 |
C2 | p3 | 41 |
C3 | p2 | 15 |
Category | Product | Cost |
C1 | p1 | 5 |
C1 | p1 | 10 |
C2 | p2 | 20 |
C2 | p3 | 10 |
C2 | p3 | 12 |
C3 | p2 | 4 |
if i use the left join, than i have a duplicate values, i can solve it by using of concatenation, does anybody have any idea how to solve it by another way?
Thanks a lot
Beck
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try Below script
Test:
LOAD Distinct
Category,
Product,
Sales
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet2);
Left Join
LOAD Category,
Product,
Cost
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet3);
Exit SCRIPT;
Also sample data attached.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It looks like you just concatenate. Is there any reason that you would want join these two? Joining usually implies adding fields from one table to another, but these two have the same fields.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jonathan thanks a lot for your Feedback,
i solved that by using of concatenation, but then i thougth, what if, if i have more table like Sales and Costs


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Well it depends on what they look like and what you need to do with them.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
my idea was: if i have for example: more than 5 Table with 2 common field, then to concatename them, to create the fact table, and my question is: is that possible in this case only by using of concatentaion?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
what is your expected output in this case???
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes you can concatenate your tables..
In one of my dashboards I concatenated 5 tables which had common fields but you'd need to create missing dimensions
Ex:
Table1
Load
A,
B,
C
0 as D
From ABC;
Concatenate
Table2:
Load
A,
B,
C,
D
From ABCD;
Concatenate
Table3:
A,
0 as B,
0 as C,
D
From AD;
and so on...
I did something just like this and got correct counts etc.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Shahbaz
thanks a lot for your feedback

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Prashant
my Output was: is that possible to join the tables with common fields via left join and avoiding the duplicates

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes you can join table with common fields avoiding duplicate.
But in as per your sample data there is no duplicate Because even if your category and product was same but there sales was different. Same for cost table
Use distinct keyword if whole line is duplicate.
Regards,
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

- « Previous Replies
-
- 1
- 2
- Next Replies »