topic Re: Combine similar alternatives eg. Email,eMail, e-mail etc. in QlikView

Combine similar alternatives eg. Email,eMail, e-mail etc.

AnnaQlik — Sat, 16 Nov 2024 19:14:15 GMT

Hi,

I want to combine similar alternatives into one alternative so the list won't be so long. You can see that several alternatives are the same but misspelled or with capital letters etc.

Can you please help me?

Re: Combine similar alternatives eg. Email,eMail, e-mail etc.

m_woolf — Fri, 24 Jan 2020 14:37:14 GMT

Create a mapping table that lists all the existing values and there corrected values. Then use Applymap to fix the data.

map_Data:
mapping
load * inline [
Value,Fixed
EMAIL,Email
Email,Email
email,Email
e-mail,Email
emial,Email

];

Data:
load
Applymap('map_Data',YourField) as YourField
from wherever;

Re: Combine similar alternatives eg. Email,eMail, e-mail etc.

AnnaQlik — Mon, 27 Jan 2020 09:45:28 GMT

Is YourField strKommunikationssaett and wherever the database strKommunikationssaett comes from?

The mapping works fine but I have trouble with applying it so that it connects to the already existing field strKommunikationssaett.

Thank you!

Re: Combine similar alternatives eg. Email,eMail, e-mail etc.

andrei_delta — Mon, 27 Jan 2020 11:25:07 GMT

hi,

you can try Capitalize(Upper(Field)) as Field

Hope it helps,

Andrew

Re: Combine similar alternatives eg. Email,eMail, e-mail etc.

AnnaQlik — Mon, 27 Jan 2020 11:48:44 GMT

Thank you, but that won't help the misspellings.

I think mapping is the solution but I have not done it before and therefore need help to understand how I connect my inline mapping with the already existing data.

Re: Combine similar alternatives eg. Email,eMail, e-mail etc.

m_woolf — Mon, 27 Jan 2020 13:02:55 GMT

Applymap('map_Data',strKommunikationssaett ) as strKommunikationssaett
from <the database strKommunikationssaett comes from>;

Re: Combine similar alternatives eg. Email,eMail, e-mail etc.

AnnaQlik — Thu, 06 Feb 2020 12:27:50 GMT

I solved it by using load inline:

load * inline [
strKommunikationssaett,strKommunikationssaettnew
e-mail, Email
E-post, Email
e-post, Email
E-postDE, Email
EMAIL, Email
Email, Email
email, Email
emial, Email
epo, Email
Epost, Email
E-mejl, Email
E-mail, Email
];