Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Avoid duplicated records after join

Hi guys,

I'm facing a problem where I have duplicated records after join of two tables.

I have got two table and I just want to merge the value of one field form one table to the other. Problem is that the field I want to merge is unique, whereas my joining fields could be repeated. I just want the first value for each occurrence.

Let's make an example:

Table1:
LOAD * inline [
  ID, Company, Code
  1, A, 4711
  2, A, 4711
  3, B, 0815
  4, B, 0815
];

DistinctTable1:
LOAD Distinct
Company,
Code
Resident Table1;

So, DistinctTable1 is fine now. But I also want field ID in this table, but just the first value. So, my table should look like this afterwards:

ID, Company, Code

1, A, 4711

3, B, 0815

I tried to use different Join and Keep statements, but since ID is unique it always gives me the whole Table1.

Any suggestion?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe

LOAD Company, Code, FirstValue(ID) as ID resident Table1 group by Company, Code;

and not using the DistinctTable1 at all.

Hope this helps,

Stefan

View solution in original post

2 Replies
swuehl
MVP
MVP

Maybe

LOAD Company, Code, FirstValue(ID) as ID resident Table1 group by Company, Code;

and not using the DistinctTable1 at all.

Hope this helps,

Stefan

Not applicable
Author

Works perfect!

Thanks very much.