topic How to avoid duplicated record be loaded in QlikView

How to avoid duplicated record be loaded

Tue, 22 Feb 2011 20:16:45 GMT

Hello,

I have problem with duplicated record need to be avoid during the loading. Below is the table source. When I load it into Qlikview, I want the first row of the duplicated record by key word to be loaded. So the result should be the table A at right. Please help.

How to avoid duplicated record be loaded

Anonymous — Tue, 22 Feb 2011 20:22:29 GMT

Try an inline query like below:

select dispute_id, id

from table_source a

where id = (select min(id)

from table_source b

where a.dispute_id = b.dispute_id)

Regards,

Dinesh.

How to avoid duplicated record be loaded

vgutkovsky — Tue, 22 Feb 2011 20:38:57 GMT

This is the QV way of getting it to work:

data:
LOAD
*
FROM datasource.qvd (qvd);

finanl_data:
NOCONCATENATE LOAD
*
RESIDENT data
WHERE NOT exists([Dispute ID],[Dispute ID])
ORDER BY [Dispute ID],ID
;

DROP TABLE data;

Regards,

How to avoid duplicated record be loaded

Tue, 22 Feb 2011 21:38:56 GMT

Thanks Vlad,

There is no record loaded in finanl_data table.

How to avoid duplicated record be loaded

vgutkovsky — Tue, 22 Feb 2011 22:14:46 GMT

Oops, I misused the exists() function. It would actually be easier to do this whole thing with a group by:

data:
LOAD
*
FROM datasource.qvd (qvd);

final_data:
NOCONCATENATE LOAD
[Dispute ID],
firstsortedvalue(ID,ID) as ID
RESIDENT data
GROUP BY [Dispute ID]
;

DROP TABLE data;

How to avoid duplicated record be loaded

johnw — Wed, 23 Feb 2011 00:28:08 GMT

I bet you could do it in one step for performance and simplicity:

LOAD
"Dispute ID"
,"ID"
FROM datasource.qvd (QVD)
WHERE not exists("Dispute ID")
;

The first time you encounter a dispute ID, it won't exist (in QlikView), so it would be loaded. Any other time you encounter that same dispute ID, it WILL exist, so should be skipped. And I think this would be an optimized QVD load.

However, this method counts on the data being written to your QVD in the correct order. If you can't guarantee that, you wouldn't want to do what I'm showing, because you'd get a "random" ID associated with the dispute ID.

How to avoid duplicated record be loaded

Wed, 23 Feb 2011 15:19:59 GMT

It is eliminated duplicated [Dispute ID] but there is no value in ID field.

How to avoid duplicated record be loaded

johnw — Wed, 23 Feb 2011 19:11:02 GMT

My approach works as posted. Vlad's works if you replace firstsortedvalue(ID,ID) with minstring(ID). I'm not sure why firstsortedvalue(ID,ID) isn't working, though. See attached.

Edit: Ah, looked it up. Firstsortedvalue() doesn't work because it requires the sort sequence to be numeric, not alphanumeric. How rude!

How to avoid duplicated record be loaded

Wed, 23 Feb 2011 19:49:23 GMT

John,

Your approach doesn't work on QV10. Check this http://community.qlik.com/forums/t/39497.aspx.

How to avoid duplicated record be loaded

vgutkovsky — Wed, 23 Feb 2011 19:50:04 GMT

Interesting, thanks for the insight John!

How to avoid duplicated record be loaded

Wed, 23 Feb 2011 19:50:51 GMT

Thanks John,

If the Dispute ID is put in order, your approach works. I have used minstring() and it works right now. Thanks again your help.

How to avoid duplicated record be loaded

Wed, 23 Feb 2011 20:25:38 GMT

Did you tried it on QV10? As I posted before, I think that it doesn't work because Exists() change of behavior. I run John example in QV10 SR1 and it brings 4 records instead of 3.

How to avoid duplicated record be loaded

Wed, 23 Feb 2011 20:40:20 GMT

You are right. It does not work on QV10.

How to avoid duplicated record be loaded

johnw — Wed, 23 Feb 2011 20:42:24 GMT

[quote user="Damián D'Onia"]John,

Your approach doesn't work on QV10. Check this http://community.qlik.com/forums/t/39497.aspx.

How annoying. Yet another failure to consider backwards compatibility when "upgrading" a feature. Way to go, QlikTech. Added my complaint to the other thread.