topic Re: Remove duplicates in QlikView

Remove duplicates

Anonymous — Thu, 13 Feb 2014 03:44:46 GMT

Hi Geniuses,

I have various columns in table but I am facing problem with three columns

Below is the small dataset of big problem

SupplierCode	SupplierName	InvoiceNo
A02	AMPTRON	654789
A0003	A HARTRODT (S) LOGISTICS PTE LTD	1305115
A0003	A HARTRODT S'PORE PTE LTD	1305115
A0003	A HARTRODT SINGAPORE PTE LTD	1305115
A03	ASEC ASIA	PIN010329
A03	ASEC ASIA	PIN010332
A03	ASEC ASIA	PIN010335
A03	ASEC ASIA	PIN010341
A03	ASEC ASIA	PIN010347
A03	ASEC ASIA	PIN010349
A03	ASEC ASIA	PIN010352
A03	ASEC ASIA	PIN010353
A03	ASEC ASIA	PIN010358
A03	ASEC ASIA	PIN010364

If you see here for the supplier A003 there are like common names I want only one name out of the 3 but for the supplier A03 as the invoices are differenrt so I wont have problem there

So in nutshell wherever there is the same supplier code and invoice number pick only one supplier name or you can say pick only one row.

Thanks,

Shashank

Re: Remove duplicates

sujeetsingh — Thu, 13 Feb 2014 03:57:44 GMT

See this sample here it is

Re: Remove duplicates

Anonymous — Thu, 13 Feb 2014 03:59:27 GMT

Were is the sample sir?

Re: Remove duplicates

Thu, 13 Feb 2014 04:13:07 GMT

Hi Shashank, You can remove there duplicates by "Exists" function.

If the Invoice is unique among these three fields then try like below:

Load SupplierCode , SupplierName , InVoiceNo

From sourcetable

where not Exists(InvoiceNo) ;

If the Invoice No is same for different SupplierCode then try like below:

Load * where not exists(Key);

Load SupplierCode , SupplierName , InVoiceNo , SupplierCode & InVoiceNo AS Key

From sourcetable ;

Please find the attached file for reference.

Re: Remove duplicates

Anonymous — Thu, 13 Feb 2014 04:13:11 GMT

My problem is not the exact names my problem is similar looking names like

A0003	A HARTRODT (S) LOGISTICS PTE LTD	1305115
A0003	A HARTRODT S'PORE PTE LTD	1305115
A0003	A HARTRODT SINGAPORE PTE LTD	1305115

Thanks,

Shashank

Re: Remove duplicates

pandiarajan — Thu, 13 Feb 2014 04:44:36 GMT

Dear Shashank,

This is my suggestion

LOAD SupplierCode,

MinString(SupplierName) as SupplierName,

InvoiceNo

FROM [http://community.qlik.com/thread/107248] (html, codepage is 1252, embedded labels, table is @1)

Group By SupplierCode,InvoiceNo;

Re: Remove duplicates

Anonymous — Thu, 13 Feb 2014 04:45:23 GMT

Hey Dathu,

I see that it works but why does "Where not exist" exclude the other records in suppliername?

Re: Remove duplicates

jagan — Thu, 13 Feb 2014 05:54:25 GMT

Hi,

Try this script

Data:

LOAD SupplierCode,

MaxString(SupplierName) AS SupplierName,

InvoiceNo

FROM [http://community.qlik.com/thread/107248] (html, codepage is 1252, embedded labels, table is @1)

GROUP BY SupplierCode, InvoiceNo;

Hope it helps you.

Regards,

Jagan.

Re: Remove duplicates

Thu, 13 Feb 2014 13:17:07 GMT

Hi Michael, the Exists function will works in the same table as well. Its like distinct load on the KEY field. If the any expression is not mentioned the field should be loaded from either previous load. In this case the previous load field assumed from same table.

Re: Remove duplicates

Thu, 13 Feb 2014 13:22:37 GMT

i would say load first your table without supplier names

then maintain another one with supplier names and codes like

LOAD code, name from table where not exist(code)

BEWARE code field has to NEVER be loaded before even in an other table under same name

ELSE start by loading this table first

Best regards

Chris

Re: Remove duplicates

rbecher — Thu, 13 Feb 2014 13:34:59 GMT

I think you should treat (or fix) the supplier duplicates in the master data source. Who knows at this level which one is correct?