Avoid duplicated records after join

[#1]

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?

[swuehl · Accepted solution]

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

[#3]

Works perfect!

Thanks very much.