topic Re: Changing names from free text fields in QlikView

Changing names from free text fields

rcorcoran — Wed, 16 Apr 2014 09:42:19 GMT

I have data in from a free text column. I want to be able to change the variants of the names that the owners have typed in so that they show as one with the data against the one name

for example there is hele and helen in for the owner helen with orders against each so in a table i want the results to show the total orders for helen which will include any she put up as hele

if anyone could help it would be great.

Thanks

Re: Changing names from free text fields

rajat2392 — Wed, 16 Apr 2014 09:48:31 GMT

count({<owner='hele*'>}orders)

try this..but what it does is after hele, whatever comes, it will count in it....

else if you know the possible names of a single owner, then while loading load it as a single name, or hard code it in expressions for all possible names.

Re: Changing names from free text fields

CELAMBARASAN — Wed, 16 Apr 2014 09:48:37 GMT

If you know the possibilities then got with Mapping table and apply map

OwnerMapping:

Mapping LOAD * Inline [

Name, OriginalName

hele, helen];

Load *, Applymap(Owner, 'OwnerMapping', Owner) AS OwnerMapText

From source;

By doing this, 'hele' will be changed to 'helen'.

Re: Changing names from free text fields

ThornOfCrowns — Wed, 16 Apr 2014 10:03:14 GMT

Are you sure you don't need a wildcard (*) in there?

i.e.

<Owner={"Hele*"}>

A problem occurs if you have distinct owners called Helen or if you have an owner called Helena

Re: Changing names from free text fields

rustyfishbones — Wed, 16 Apr 2014 10:06:32 GMT

Hi Rhona,

The best way to manage this would be by using a Mapping Table

like what celambarasan has suggested

Regards

Alan

Re: Changing names from free text fields

rajat2392 — Wed, 16 Apr 2014 10:17:05 GMT

please read the second line also...