Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
samuel_lin
Creator
Creator

Load an additional table for data normalization?

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

11 Replies
sunny_talwar

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;

datanibbler
Champion
Champion

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

samuel_lin
Creator
Creator
Author

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

samuel_lin
Creator
Creator
Author

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?

sunny_talwar

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

samuel_lin
Creator
Creator
Author

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

samuel_lin
Creator
Creator
Author

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.

sunny_talwar

Sounds good

samuel_lin
Creator
Creator
Author

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