Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlikers!
I have a list of companies for example: google, Google, GOOGLE, google.com and I want all of these to be just Google.
If there a way that I can load a table into Qlikview and normalized the name using that table as a reference.
Such a table in my mind would be:
Normalized Name Name1 Name2 Name3 Name4
Google GOOGLE google google.com Google
any thought?
Thanks!
Samuel
Try with Mapping Load
MappingNames:
Mapping
LOAD * Inline [
Variation, NormalizedName
GOOGLE, Google
google, Google
google.com, Google
Google, Google
];
MainTable:
LOAD yourFields,
CompanyName,
ApplyMap('MappingNames', CompanyName) as NormalizedCompanyName
FROM source;
Hi Samuel,
how about just using the UPPER() function to have the word all uppercase, no matter what, and just removing those endings by way of the SUBFIELD() function? Wouldn't that do as well - in this example anyway ...
Best regards,
DataNibbler
Hello DataNibbler,
I considered UPPER before, but for the cases of google.com, BCG (or Boston Consulting Group), Upperand SUBFIELD wouldn't meet the need for this purpose. Thank you for thinking through this for me, really do appreciate it!
Samuel
Hi Sunny,
this will do, so does that mean for the company names that do not require normalization, I also need to have a line (in the load inline) for it?
Not really, I think this should help:
MappingNames:
Mapping
LOAD * Inline [
Variation, NormalizedName
GOOGLE, Google
google, Google
google.com, Google
Google, Google
];
MainTable:
LOAD yourFields,
CompanyName,
ApplyMap('MappingNames', CompanyName, CompanyName) as NormalizedCompanyName
FROM source;
Now wherever the CompanyName doesn't find a hit in the Mapping Table, it will just return the original CompanyName
Thanks, Sunny.
Are you sure that I can add ApplyMap in the section of the MainTable? When I tried it, with loading inline first on the top, basically the same way you lay out, but I got error loading messages.
and this line: ApplyMap('MappingNames', CompanyName, CompanyName) as NormalizedCompanyName,
doesn't make sense to me, should it refer to NormalizedName somewhere in the ApplyMap function?
Thanks so much for your help!
Samuel
actually, nevermind, I think i got it to work, I forgot to type comma after the line for next line, let me test and keep you posted.
Sounds good
The mapping works, thank you very much.
I wonder, is there a way to do wild mapping? such as long as i have "google" in a string: "amazonapplegooglesamsung" to be normalized as google? and even to be case insensitive?
thanks!
Samuel