Discussion Board for collaboration related to Qlik Education.
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!
Go to Solution.
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:
Load * INLINE [
ID as newID, Name as newName
where not exists(newID,ID);
Btw, your code works as well if you change
resident [Table1] WHERE ID=$(vID);
resident [Table1] WHERE ID='$(vID)';
just vary the code very slightly and you'll have it:
LOAD ID, B as NameFROMSample_20140903.xlsx(ooxml, embedded labels, table is Sheet1);Name_IDs_v2:LOAD Distinct ID as Dist_ID, Name as Dist_nameRESIDENT 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.
MaxString(Name) as Name,
concat(Name, '---') as AllNames
Resident tmp group by ID;
DROP Table tmp;
Thanks a lot!
To get the result that I need it still requires where not exists([Dist_ID],ID) condition. Thanks, though!