topic Re: I have a dbase with so many unstructured data in QlikView

I have a dbase with so many unstructured data

Fri, 13 Feb 2015 18:10:37 GMT

Hello,

How do I deal with Fields that have mixed alphabets e.g

Header 1	Header 2	Header 3
S/N	Name	Office
1	Mark	GM's Office
2	mark	gm's Office
3	mk	gm office

These are information of one person is there a way i can make Qlikview know these data are just one persons data?

I want Qlikview to see Mark, mark and mk as Mark

Re: I have a dbase with so many unstructured data

robert_mika — Fri, 13 Feb 2015 18:13:04 GMT

Is that only one example or you have more like

Lets say Dv will be David?

Re: I have a dbase with so many unstructured data

Anonymous — Fri, 13 Feb 2015 18:17:44 GMT

You can do something like

if(wildmatch('m*k'),'Mark') as Name

and

if(wildmatch('gm*office'),'GM Office') as Office

But you cannot predict all possible typos. Better to clean up in the data...

Re: I have a dbase with so many unstructured data

Anonymous — Fri, 13 Feb 2015 18:33:16 GMT

You can make a separate table which can have all the variations and corresponding normalized name. like mk, mak, MRk as MARK. Join this table with your original table on variations and upload the data with normalized field values.

Re: I have a dbase with so many unstructured data

Fri, 13 Feb 2015 22:20:04 GMT

My data source is SAP and i dont have access to do thorough data cleansing.

Re: I have a dbase with so many unstructured data

Anonymous — Fri, 13 Feb 2015 22:23:41 GMT

So it will be pretty much "manual" fixes in QV...

Search the forum for the "wildcard mapping", I believe there are solutions than can help, at least partially.

Re: I have a dbase with so many unstructured data

Anonymous — Sun, 15 Feb 2015 11:40:42 GMT

The QlikView Capitalize() function could be of use to you. Below pasted from the Help:

capitalize(s )

Returns the string s with all words capitalized.

Example:

capitalize ('my little pony') returns 'My Little Pony'

capitalize ( 'AA bb cC Dd') returns 'Aa Bb Cc Dd'

Re: I have a dbase with so many unstructured data

Fri, 14 Oct 2016 18:57:27 GMT

Hi Emma did you achieved it

Re: I have a dbase with so many unstructured data

robert_mika — Fri, 14 Oct 2016 19:06:28 GMT

I can not see any other way than with ApplyMap/Mapping Load

Don't join - use Applymap instead