topic Re: Load an additional table for data normalization? in QlikView

Load an additional table for data normalization?

samuel_lin — Mon, 26 Oct 2015 21:43:39 GMT

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

Re: Load an additional table for data normalization?

sunny_talwar — Tue, 27 Oct 2015 15:38:55 GMT

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;

Re: Load an additional table for data normalization?

datanibbler — Tue, 27 Oct 2015 15:40:51 GMT

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

Re: Load an additional table for data normalization?

samuel_lin — Tue, 27 Oct 2015 16:14:00 GMT

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

Re: Load an additional table for data normalization?

samuel_lin — Tue, 27 Oct 2015 16:15:17 GMT

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?

Re: Load an additional table for data normalization?

sunny_talwar — Tue, 27 Oct 2015 16:31:20 GMT

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

Re: Load an additional table for data normalization?

samuel_lin — Tue, 27 Oct 2015 17:17:52 GMT

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

Re: Load an additional table for data normalization?

samuel_lin — Tue, 27 Oct 2015 17:24:45 GMT

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.

Re: Load an additional table for data normalization?

sunny_talwar — Tue, 27 Oct 2015 17:38:46 GMT

Sounds good

Re: Load an additional table for data normalization?

samuel_lin — Wed, 28 Oct 2015 17:01:06 GMT

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

Re: Load an additional table for data normalization?

Anonymous — Wed, 28 Oct 2015 18:41:37 GMT

you can't do case insensitive directly...but by using upper() or lower() for that matter and forcing all text values to one case for comparison purposes only, you can get around this.

as far as the wild mapping is concerned, try index() function.

=index('amazonapplegooglesamsung', 'google')

Re: Load an additional table for data normalization?

MarcoWedel — Thu, 29 Oct 2015 00:29:50 GMT

Hi,

maybe helpful:

if condition

regards

Marco