Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

Unique IDs with singular related records

Dear QV developers,

I would highly appreciate your advice on the following issue.

Let there is a table with a list of IDs and Names. Due to wrong (misspelled) Names IDs are non-unique. For instance, there are entries like these:

[ID],    [Name]
11,      "Tom"
11,      "TOM"
23,      "Janny"
23,      "Ja_ny"
33,      "John"
43,      "Michael"

The aim is to get a table with unique IDs and singular Names. And it does not matter if grammatically right or wrong name is assigned to a particular ID.

I have found a solution, which deals the issue.

A clip from my code:

[Table1]:
Load * INLINE [
11,      "Tom"
11,      "TOM"
23,      "Janny"
23,      "Ja_ny"
33,      "John"
43,      "Michael"
]
;

[Table2]:
Load
Distinct(ID) AS [Dist_ID]
resident [Table1]
;

For v_i = 0 to (NoOfRows('Table2')-1)
Let vLine = $(v_i);
Let vID = Peek('Dist_ID',vLine,'Table2');
First 1
LOAD [ID]             AS [NewID],
     
[Name]         AS [NewName]
resident [Table1] WHERE ID=$(vID);
Next v_i

However, if the IDs are non-integers:

[ID],    [Name]
aa11,    "Tom"
aa11,    "TOM"
bb23,    "Janny"
bb23,    "Ja_ny"
ba33,    "John"
ab43,    "Michael"

The code does not work. May you, please, advise me on this issue? Perhaps there exists a better solution, than the one I have found?

Thanks in advance!

Tags (2)
1 Solution

Accepted Solutions
Employee
Employee

Re: Unique IDs with singular related records

Hi Benas,

If we assume that you want the first name found for the ID (and don't care if this is the "right" name), then simply use a where exists() statement, something like:

tmp:

Load * INLINE [

ID,    Name

aa11,    Tom

aa11,    TOM

bb23,    Janny

bb23,    Ja_ny

ba33,    John

ab43,    Michael

];

Load

     ID as newID, Name as newName

Resident tmp

where not exists(newID,ID);

Btw, your code works as well if you change

resident [Table1] WHERE ID=$(vID);


to


resident [Table1] WHERE ID='$(vID)';

5 Replies
datanibbler
Esteemed Contributor

Re: Unique IDs with singular related records

Hi Benas,

just vary the code very slightly and you'll have it:

Name_IDs_v1:

LOAD ID,
    
B as Name
FROM
Sample_20140903.xlsx
(
ooxml, embedded labels, table is Sheet1);

Name_IDs_v2:
LOAD
   
Distinct ID as Dist_ID,
   
Name as Dist_name
RESIDENT Name_IDs
;
DROP TABLE Name_IDs;

Voila, there is a unique record with every distinct ID and a name attached. Basically your code with just one line added.

Best regards,

DataNibbler

Employee
Employee

Re: Unique IDs with singular related records

Hi Benas,

If we assume that you want the first name found for the ID (and don't care if this is the "right" name), then simply use a where exists() statement, something like:

tmp:

Load * INLINE [

ID,    Name

aa11,    Tom

aa11,    TOM

bb23,    Janny

bb23,    Ja_ny

ba33,    John

ab43,    Michael

];

Load

     ID as newID, Name as newName

Resident tmp

where not exists(newID,ID);

Btw, your code works as well if you change

resident [Table1] WHERE ID=$(vID);


to


resident [Table1] WHERE ID='$(vID)';

MVP
MVP

Re: Unique IDs with singular related records

tmp:

Load * INLINE [

ID,    Name

aa11,    Tom

aa11,    TOM

bb23,    Janny

bb23,    Ja_ny

ba33,    John

ab43,    Michael

];

tmp2:

NoConcatenate load

ID,

MaxString(Name) as Name,

concat(Name, '---') as AllNames

Resident tmp group by ID;

DROP Table tmp;

Not applicable

Re: Re: Unique IDs with singular related records

Björn,

Thanks a lot!

Not applicable

Re: Unique IDs with singular related records

To get the result that I need it still requires where not exists([Dist_ID],ID) condition. Thanks, though!

Community Browser