Avoid duplicated records after join

Wed, 16 May 2012 16:44:47 GMT

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?

Avoid duplicated records after join

swuehl — Wed, 16 May 2012 17:35:42 GMT

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

Avoid duplicated records after join

Thu, 17 May 2012 16:38:56 GMT

Works perfect!

Thanks very much.

topic Avoid duplicated records after join in QlikView

Avoid duplicated records after join

Avoid duplicated records after join

Avoid duplicated records after join