topic Re: reasons for duplicate rows in QlikView

reasons for duplicate rows

Thu, 19 Jun 2014 08:50:40 GMT

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.

Re: reasons for duplicate rows

anbu1984 — Thu, 19 Jun 2014 08:58:35 GMT

Try join instead of concatenation

Re: reasons for duplicate rows

Anonymous — Thu, 19 Jun 2014 08:59:35 GMT

can you pls share the datamodel picture?

Re: reasons for duplicate rows

jonathandienst — Thu, 19 Jun 2014 09:02:25 GMT

You will need to join rather than concatenate to get them on the same row. Post more details of your data and script for more detailed help on how to do so.

HTH

Jonathan

Re: reasons for duplicate rows

Thu, 19 Jun 2014 09:06:34 GMT

Oh yes, sorry I meant join. I used 'outer join'.

Re: reasons for duplicate rows

Thu, 19 Jun 2014 09:07:01 GMT

I have used 'outer join'.

Re: reasons for duplicate rows

Thu, 19 Jun 2014 09:08:58 GMT

Is there any other way for you to help me? Because I can't post the data...so sorry.

Re: reasons for duplicate rows

Thu, 19 Jun 2014 09:09:19 GMT

Sorry, what is datamodel picture?

Re: reasons for duplicate rows

manojkulkarni — Thu, 19 Jun 2014 09:09:38 GMT

concatenate will always add separate row, but join on primary key can help you to remove duplicate

Re: reasons for duplicate rows

jonathandienst — Thu, 19 Jun 2014 09:13:01 GMT

There is something wrong with your join. To get help, you will need to post your load script and some more details of your data. Best solution is to upload your model here.

Jonathan

Re: reasons for duplicate rows

Thu, 19 Jun 2014 09:16:25 GMT

What's the script to join on primary key? cos all I did was outer join(tablename)

Re: reasons for duplicate rows

Thu, 19 Jun 2014 09:18:40 GMT

ItemClassification:

LOAD *, text(ITEMNUM) as itemnum;

LOAD ITEMNUM, .

.(other fields)

FROM

(biff, embedded labels, table is Sheet1$);

outer Join(ItemClassification)

LOAD *, ltrim(text(ITEMNUM)) as itemnum;

LOAD ITEMNUM, .

FROM

(biff, embedded labels, table is Sheet1$);

outer Join(ItemClassification)

LOAD *, ltrim(text(ITEMNUM)) as itemnum;

LOAD ITEMNUM,.

FROM

(ooxml, embedded labels, table is (filename));

Re: reasons for duplicate rows

manojkulkarni — Thu, 19 Jun 2014 09:22:03 GMT

Outer join (Table), shouldn't be giving u duplicate rows.

Can u paste sample data

Re: reasons for duplicate rows

Anonymous — Thu, 19 Jun 2014 09:25:46 GMT

Just a guess

Try ltrim on first table as well and let me know if that causes the issue

Re: reasons for duplicate rows

Thu, 19 Jun 2014 09:25:52 GMT

Is there another way to match the itemnum?

Re: reasons for duplicate rows

swuehl — Thu, 19 Jun 2014 09:29:24 GMT

Sure,

if you can't fix your original join, you can use a resident load with a group by:

LOAD

itemnum,

only(site),

only(name),

only(balance),

only(class),

only(code),

only(Group)

RESIDENT YourTable

GROUP BY itemnum;

edit: I think your issue is that primary keys itemnum don't match properly, check the values returned on length, existing white spaces etc. Your data should return numbers for the primary keys, right?

Re: reasons for duplicate rows

Thu, 19 Jun 2014 09:33:29 GMT

but how do I use this for multiple tables? the fields come from different tables.

Re: reasons for duplicate rows

manojkulkarni — Thu, 19 Jun 2014 09:34:01 GMT

check below script

T1:

LOAD * INLINE [

No, site, name, class

1, A, XYZ, D

2, B, ABC, A

3, A, PQR, A

];

Outer join (T1)

LOAD * INLINE [

No, Code, Grp

1, 1a, AA

2, 1b, ABC, DD

3, 2a, PQR, AB

];

Re: reasons for duplicate rows

Thu, 19 Jun 2014 09:34:31 GMT

I convert to text because some itemnum have letters

Re: reasons for duplicate rows

Thu, 19 Jun 2014 09:35:36 GMT

I can't, there are 10,000 over records...