How to avoid duplicated record be loaded

[#1]

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.

[Anonymous]

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.

[vgutkovsky]

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,

[#4]

Thanks Vlad,

There is no record loaded in finanl_data table.

[vgutkovsky]

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;

[johnw]

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.

[#7]

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

[johnw]

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!

[#9]

John,

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

[vgutkovsky]

Interesting, thanks for the insight John!