Removing Duplicates with NULL field in another column?

[#1]

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!

[MK_QSL]

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;

[pokassov]

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

  t1

Order by

  Name,

  Tag desc;

DROP Table t1;

[Anonymous]

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)

;

[maxgro]

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;