topic Re: How to remove duplicates in a file in QlikView

How to remove duplicates in a file

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

How to remove duplicate values in the file.

Example:

date barcode

01-05-2016 123

02-05-2016 456

03-06-2016 789

05-06-2016 123

09-04-2016 456

output:

date barcode

01-05-2016 123

09-04-2016 456

03-06-2016 789

its an urgent requirement can u pls help me......

Regards,

Ravi.

Re: How to remove duplicates in a file

trdandamudi — Sun, 01 May 2016 10:14:29 GMT

May be like this:

Data:

Load * inline [

date, barcode

01-05-2016, 123

02-05-2016, 456

03-06-2016, 789

05-06-2016, 123

09-04-2016, 456

];

NoConcatenate

Final:

Load *

Where Count =1;

Load

date,

barcode,

If(barcode<>Previous(barcode),1,0) as Count

Resident Data

Order by barcode;

Drop Table Data;

Re: How to remove duplicates in a file

MindaugasBacius — Sun, 01 May 2016 12:02:13 GMT

As I understand you have loaded a table:

date barcode

01-05-2016 123

02-05-2016 456

03-06-2016 789

05-06-2016 123

09-04-2016 456

You wish to have a result like this:

date barcode

01-05-2016 123

09-04-2016 456

03-06-2016 789

Am I right?

UPDATE: what is the logic while picking up the dates?

Re: How to remove duplicates in a file

sunny_talwar — Sun, 01 May 2016 13:18:21 GMT

Ravi you need to provide us with the logic to select those dates. Are you selecting Max(Date) for a particular barcode or a minimum date for a particular date? first value or last value?

Also, provide the information about your date, is it MM-DD-YYYY or DD-MM-YYYY. The dates you have provided can be in any format. It would help to add a date like 21-06-2016 or 06-21-2016 when providing a sample, so that we can differentiate between the format

Re: How to remove duplicates in a file

MarcoWedel — Sun, 01 May 2016 20:06:14 GMT

Hi,

maybe helpful:

table1:
LOAD * INLINE [
    date, barcode
    01-05-2016, 123
    05-06-2016, 123
    09-04-2016, 456
    02-05-2016, 456
    03-06-2016, 789
]
Where not Exists(barcode);

regards

Marco

Re: How to remove duplicates in a file

Fri, 06 May 2016 11:49:51 GMT

Exact my problem is......

I have data like this......1000 records

sample data:

Barcode create_date prepared _By

123 24-04-2016 ravi

124 24-04-2016 teja

123 29--04- 2016 ---

345 26-04-2016 ----

346 26-04-2016 ---

345 29-04-2016 ---

I want these outputs:

0utput1:

Barcode create date prepared _By

123 24-04-2016 ravi

124 24-04-2016 teja

345 26-04-2016 ----

346 26-04-2016 ---

In above output using where condition prepared _By =''--' output2 will come no problem.

0utput2:

Barcode create date prepared _By

345 26-04-2016 ----

346 26-04-2016 ---

my main problem is output1 is not getting perfectly while using Thirumal script....its workinh some records its removing fst record and some times second records its removing.....In duplicate want remove only seconds time reapted records.

regards,

Ravi

Re: How to remove duplicates in a file

swuehl — Fri, 06 May 2016 11:59:33 GMT

Have you tried already Marco's suggestion using

WHERE NOT EXISTS(Barcode);

Please note that this will not reproduce your original sample:

date barcode

01-05-2016 123

02-05-2016 456 // why not pick this record?

03-06-2016 789

05-06-2016 123

09-04-2016 456

output:

date barcode

01-05-2016 123

09-04-2016 456

03-06-2016 789

Re: How to remove duplicates in a file

trdandamudi — Fri, 06 May 2016 14:38:37 GMT

The below script will remove the second time repeated records:

Data:

Load * inline [

date, barcode

01-05-2016, 123

02-05-2016, 456

03-06-2016, 789

05-06-2016, 123

09-04-2016, 456

];

NoConcatenate

Final:

Load *

Where Count =1;

Load

barcode,

date,

If(barcode<>Previous(barcode),1,0) as Count

Resident Data

Order by barcode,date;

Drop Table Data;

Hope this is what you are looking for.

Re: How to remove duplicates in a file

trdandamudi — Fri, 06 May 2016 14:45:24 GMT

The below script is based on your new data:

Data:

Load * Inline [

Barcode, create_date, prepared_By

123, 24-04-2016, ravi

124, 24-04-2016, teja

123, 29-04-2016,

345, 26-04-2016,

346, 26-04-2016,

345, 29-04-2016,

];

NoConcatenate

Final:

Load *

Where Count =1;

Load

Barcode,

create_date,

prepared_By,

If(Barcode<>Previous(Barcode),1,0) as Count

Resident Data

Order by Barcode,create_date;

Drop Table Data;

Re: How to remove duplicates in a file

Mon, 09 May 2016 09:18:01 GMT

Thank you Now its working fine...