topic Re: remove duplicate records in QlikView

remove duplicate records

manoj217 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

i have a question i want to remove duplicate records as well as their real values also

load * inline [id,value

1,10

1,4

2,3

3,5

4,6];

i want to get

id,value

2,3

3,5

4,6

perform any action in script only

Re: remove duplicate records

Anil_Babu_Samineni — Thu, 26 Oct 2017 09:47:25 GMT

May be this?

Sample:

load * inline [

id,value

1,10

1,4

2,3

3,5

4,6

];

Final:

LOAD id, Count(id) as ID1 Resident Sample Group By id;

Inner Join

LOAD * Resident Final Where ID1 <> 2;

DROP Table Sample;

DROP Field ID1;

Re: remove duplicate records

techvarun — Thu, 26 Oct 2017 09:49:35 GMT

Like this?

Test:
load * inline [id,value
1,10
1,4
2,3
3,5
4,6] ;




Test1:
Load  Count(id) as Count,id Resident Test  Group by id;
Left join(Test1)
Load * Resident Test;
Drop table Test;

Load id,value Resident Test1 where Count =1;
Drop Table Test1;

Re: remove duplicate records

RonaldDoes — Thu, 26 Oct 2017 09:51:41 GMT

Hi Manoj,

Here's a solution I think would work for you.

SourceData:
load * inline
[id,value
1,10
1,4
2,3
3,5
4,6];

LEFT JOIN (SourceData)
LOAD
id,
count(id) as duplicate
Resident SourceData Group By id;

Final:
NoConcatenate LOAD
id,
value
Resident SourceData Where duplicate = 1;

DROP Table SourceData;

Please find attached demo.

With kind regards,

Ronald

Re: remove duplicate records

antoniotiman — Thu, 26 Oct 2017 09:55:02 GMT

Temp:
LOAD id,Only(value) as value,Count(id) as Count inline [id,value
1,10
1,4
2,3
3,5
4,6]
Group By id;
LOAD id,value
Resident Temp Where Count=1;
Drop Table Temp;

Re: remove duplicate records

surendraj — Thu, 26 Oct 2017 09:56:23 GMT

Test:

load * inline [

id,value

1,10

1,4

2,3

3,5

4,6

];

Test1:

LOAD id, Count(id) as count Resident Sample Group By id;

Inner Join

LOAD * Resident Test1 Where ID1 = 1;

DROP Table Test;

DROP Field count;