reasons for duplicate rows

[#1]

Hi, why are there duplicate rows? I concatenated tables from different sources and 'itemnum' is primary key. The itemnum appears in all the tables and has data for all columns but still duplicates, for example, it has data for all the columns from one table but is null for the other columns in other tables. So there is duplicate like below:

itemnum	site	name	balance	class	code	Group
1009	A	D	-	B	-	-
1009	-	-	45	-	FG	H

How can I combine everything to one row (distinct itemnum)?

itemnum	site	name	balance	class	code	Group
1009	A	D	45	B	FG	H

Thanks.

[swuehl]

Again, I would start looking at the key values that create the duplicate records. Select them in your table, look at them in a list box: Does QV show only one or multiple entries in a list box? If QV show multiple entries, your key values are distinct. check the distinct values, in what they differ (length? using len() ).

Looking at your script, you handle the keys differently in the tables, regarding the use of ltrim() function.

I would suggest applying trim() to all occurrences.

[manojkulkarni]

can you give sample of 3-4 lines of data from each table

[jonathandienst]

Rachel

The join should have one or more key fields (ie exist in both the join target and the join source), which select the record to join with, and one or more additional fields which exist in the join source but do not yet exist in the target. If they already exist, then they are considered to be part of the key field. To illustrate

LOAD A, B, C From Table1;

Join(Table1)

LOAD A, B, D From Table2;

This will add column D to Table1 where the A, B key fields match.

If you do this (which I think is the same as you are doing):

Outer Join(Table1)

LOAD A, B, C From Table2;

Then you will add no new column and you will add new rows where the A,B,C compound key from Table2 is not found in Table1. It will not populate missing C values.

HTH

Jonathan

[#5]

sample data:

[hitha1512]

@jonathandienst Hello, I have a similar issue and i am unable to fix it. Could you please help. 

https://community.qlik.com/t5/App-Development/Outer-Join-resulting-in-additional-rows/td-p/2040473