topic Re: Function to match two items in QlikView

Function to match two items

Thu, 27 Mar 2014 12:36:11 GMT

HI All,

I am not sure this can be done but wanted to throw it out and see if anyone can help:

The Problem: I have two tables with Vendor Information listed and the vendor is spelled slightly different in both tables. Is there a way to do some match, fix, or anything else to correct the issue. Here is an example of what I am talking about:

Vendor Table 1

AEP-Energy

Vendor Table 2

AEPEnergy

In other words the Vendor name in both list is the same but spelled slightly different. Is there any tricks out there.

David

Re: Function to match two items

alexandros17 — Thu, 27 Mar 2014 12:38:38 GMT

THe easiest way is this:

I think you have a vendor code, so startting from it create a xlsx (or a table) with code and correct name.

Load that xlsx and use its name as the only one

Re: Function to match two items

stabben23 — Thu, 27 Mar 2014 12:39:30 GMT

Hi David,

the purgchar('field','-') is one example.

Re: Function to match two items

sudeepkm — Thu, 27 Mar 2014 12:54:38 GMT

You can use

1. Upper or lower functions if both the field values have same name but in different case

2. purgechar to remove cuh characters you find as a difference in between two names

3. trim for any spaces around the names

Re: Function to match two items

ashwanin — Thu, 27 Mar 2014 12:55:44 GMT

use Wildmatch function

Re: Function to match two items

jerem1234 — Thu, 27 Mar 2014 12:59:17 GMT

Another option is to do a Mapping like:

map1:

Mapping Load * Inline [

Column1,Column2

AEP-Energy, AEPEnergy

...

];

Where AEP-Energy is the text it is in and AEPEnergy is the text you want it to be. Then do an apply map when you load field like:

applymap('map1', FIELD) as FIELD

Hope this helps!

Re: Function to match two items

nizamsha — Thu, 27 Mar 2014 13:11:28 GMT

Replace( Fileld,'-','')

if it was like this mean AEP-Energy Aep-Energy

use this one

upper(Replace( Field,'-',''))

Lower(Replace(Field,'-',''))

use Purgechar(field,'-')

Re: Function to match two items

sunilkumarqv — Thu, 27 Mar 2014 13:23:58 GMT

// Rename the Field:

FieldMap:

Mapping SQL SELECT oldnames, newnames FROM datadictionary;

Rename fields using FieldMap;

// Rename the Table

Tab1:

Select * from Trans;

Rename table Tab1 to Xyz;

TabMap:

Mapping load oldnames, newnames from tabnames.csv;

Rename tables using TabMap;

// Rename the Fields

Alias ID as CustomerID;

Load * from Customer.csv;

Load ID as CustomerID, Name, Address, Zip, City, State from Customer.csv;