topic Removing Duplicates with NULL field in another column? in QlikView

Removing Duplicates with NULL field in another column?

Fri, 10 Apr 2015 15:49:51 GMT

Hi,

I have the following load statement similar to:

LOAD

name as name

tag as tag

FROM

names.qvd

the data looks like

Name	Tag
Joe Dow	null
Robert James	Marine
Robert James	null
Abraham	Sportsman
Abraham	Pilot
Abraham	null

I want to remove duplicate names that have their tag as null; in the above sample data {Robert James, null} and {Abraham,null} would get deleted. Joe Dow is a valid case as it is unique entry and there are no duplicates.

How can I do this? Please help! Thanks!

Re: Removing Duplicates with NULL field in another column?

MK_QSL — Fri, 10 Apr 2015 16:29:14 GMT

Table1:

Load

name,

If(UPPER(Tag) = 'NULL' or Len(Trim(Tag))=0 or IsNull(Tag),Null(),Tag) as Tag

Inline

[

name, Tag

Joe Dow, null

Robert James, Marine

Robert James, null

Abraham, Sportsman

Abraham, Pilot

Abraham, null

];

Left Join (Table1)

Load name, Count(name) as TotalTag Resident Table1 Group By name;

Final:

Load name, Tag Resident Table1 Where TotalTag = 1 or (TotalTag > 1 and Not IsNull(Tag));

Drop Table Table1;

Re: Removing Duplicates with NULL field in another column?

pokassov — Fri, 10 Apr 2015 16:36:58 GMT

Hello!

I don't have your qvd, so I used inline instead of.

Please, check my script

Set NullInterpret = '';

t1:

LOAD * Inline [

Name,Tag

Joe Dow,

Robert James,Marine

Robert James,

Abraham, Sportsman

Abraham, Pilot

Abraham,

];

t2:

NoConcatenate

LOAD

Name,

Tag

Where

Flag=1;

LOAD

Name,

Tag,

if(Previous(Name)=Name and IsNull(Tag),0,1) as Flag

Resident

Order by

Name,

Tag desc;

DROP Table t1;

Re: Removing Duplicates with NULL field in another column?

Anonymous — Fri, 10 Apr 2015 17:55:25 GMT

You can write it as below as well,

Table1:

LOAD

name,

tag,

FROM

names.qvd

where len(trim(tag)) >0

concatenate(Table1)

LOAD

name,

tag

FROM

names.qvd

where not exists(name)

;

Re: Removing Duplicates with NULL field in another column?

maxgro — Fri, 10 Apr 2015 18:50:32 GMT

Set NullInterpret = '';

t1: LOAD * Inline [

Name,Tag

Joe Dow,

Robert James,Marine

Robert James,

Abraham, Sportsman

Abraham, Pilot

Abraham,

];

t2: NoConcatenate load *

Resident t1

where not (IsNull(Tag) and Peek(Name)=Name)

order by Name, Tag desc;

DROP Table t1;