topic Re: how to remove duplicates from table in QlikView

how to remove duplicates from table

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

How to remove duplicates from table where multiple keys are involved.

In my case , I want to remove duplicates where [Main Project Name], SKUID and [PMaterial Number] is same.

LOAD

[Main Project Name],

[Project Status],

SKUID,

[SKU Status],

[PMaterial Number],

[PMaterial Status]

FROM

Project_list_status.xlsx

(ooxml, embedded labels, table is Sheet1);

Can you please help.

Thanks in advance.

Re: how to remove duplicates from table

Anil_Babu_Samineni — Thu, 13 Apr 2017 16:54:52 GMT

Can you provide sample data on wall, May be inline memory data. Qlik can remove Duplicates automatically while loading script

Re: how to remove duplicates from table

Thu, 13 Apr 2017 17:02:45 GMT

Please find attached image where 2 records are shown out of which only first one should be shown.

Re: how to remove duplicates from table

Anil_Babu_Samineni — Thu, 13 Apr 2017 17:05:50 GMT

Can you post those on wall not the image, Interesting seems all are same. Will check from my end

Re: how to remove duplicates from table

Thu, 13 Apr 2017 18:27:05 GMT

I have attached sample data where I need single row based on comparison of project name , SKU and PMaterial.

Re: how to remove duplicates from table

vishsaggi — Thu, 13 Apr 2017 19:43:05 GMT

When you load this excel in Qlikview what is happening? you see two rows or only 1 row. For me i can see only one row of data. ?

Re: how to remove duplicates from table

Anil_Babu_Samineni — Fri, 14 Apr 2017 01:53:27 GMT

Even, I got one line itself. I think it mistake because you saved after edit there it could be fault with different character. So, The all characters should be same and case-sensitive.

Re: how to remove duplicates from table

avinashelite — Fri, 14 Apr 2017 04:24:02 GMT

I think you have not loaded the data to Qlikview and see the data only in excel ...load the data to QV and check if the data is same then QV will so that as always one

Re: how to remove duplicates from table

rahulpawarb — Fri, 14 Apr 2017 05:20:06 GMT

Hello Dipak,

Please refer attached sample application.

Regards!

Rahul

Re: how to remove duplicates from table

Sat, 15 Apr 2017 09:33:53 GMT

HI Anil,

Yes you see only one record in the Qlikview output but when you store these contents in QVD file it shows two records and that is where i am facing problem i am putting this table in loop to generate new table but stuck due to issue with duplicate records.I am attaching qvd file for reference.

Re: how to remove duplicates from table

Sat, 15 Apr 2017 09:39:16 GMT

attached qvd file.it is showing two records.

Re: how to remove duplicates from table

Anonymous — Sat, 15 Apr 2017 09:45:46 GMT

TABLE:

LOAD

Distinct

[Main Project Name],

[Project Status],

[Project Start Date],

SKUID,

[SKU Description],

[SKU Status],

[PMaterial Number],

[PMaterial Status],

date

FROM

[data.qvd]

(qvd);

STORE TABLE into distinct.qvd (qvd);

Re: how to remove duplicates from table

parul_mehta — Mon, 17 Apr 2017 12:05:45 GMT

Hi Dipak , try Robin Hausdörfer solution, it is working for me.

Re: how to remove duplicates from table

evan_kurowski — Mon, 17 Apr 2017 16:30:21 GMT

Hello Dipak, give this a try

//This should be a DISTINCT list of %KEY_UNIQUE_SUBSET_OF_FIELDS
[UNIQUE_SUBSET_OF_FIELDS]:
LOAD DISTINCT
AutoNumberHash128([Main Project Name], SKUID, [PMaterial Number]) AS %KEY_UNIQUE_SUBSET_OF_FIELDS,
[Main Project Name],
[PMaterial Number],
SKUID
FROM
Project_list_status.xlsx;

//These rows can be a 1 to many per %KEY_UNIQUE_SUBSET_OF_FIELDS
[NON_UNIQUE_ATTRIBUTES]:
LOAD
AutoNumberHash128([Main Project Name], SKUID, [PMaterial Number]) AS %KEY_UNIQUE_SUBSET_OF_FIELDS,
[SKU Status],
[Project Status],
[PMaterial Status]
FROM
Project_list_status.xlsx;

Re: how to remove duplicates from table

Sun, 23 Apr 2017 10:04:18 GMT

Thanks to all .it is working for me.Distinct keyword is checking all the field combination and maintaining unique record in qvd file as well as during display in qlikview. Thanks to all.