List manipulation help needed!

[#1]

Hi everyone,

I have a slight problem regarding my list within a field.

I have a numbver of entries that are meant to be recognised as the same but they spelt differently, eg

FX Support, fx support, FXSupport, FXsupport

Qlikview views these as different names but I would like them grouped as one. Is there any way in which this can be done?

Regards,

[#2]

Hi,

Add the following expression to the script:

IF ( UPPER ( [Field] ) LIKE ' FX* ' , 'FX Support' , [Field] ) AS GroupedField,

Regards, Sander

[#3]

Hi Sander,

Thanks for this. However, would this not create a problem if I had FX Currency Options as an entry, I don't want this grouped along with FX support.

Any thoughts?

[#4]

Yes, that would sure will....

I don't know the exact dependencies of your fieldvalues but if the values always start with 'FX' and always contain 'SUPPORT' this would work:

IF ( UPPER ( [Field] ) LIKE ' FX* ' AND UPPER ( [Field] ) LIKE ' *SUPPORT* ' , 'FX Support' , [Field] ) AS GroupedField,

You can fiddle around with this expressions, adjusting them to your own needs.. Let me know if this did the trick?

Regards, Sander

[#5]

Hi,

You can convert that one into either lower or upper case and remove the space between them.

Regards,

Ravi.....

[rwunderlich]

If the only differences are casing and spaces, you could regularize them as Ravi suggested and then map to display name using a mapping table:

mymap:
MAPPING LOAD * INLINE [
from, to
fxsupport, FX Support
fxcurrencyoptions, FX Currency Options
];
applymap('mymap', lower(replace(field, ' ', '')) as DisplayName

If the differences are more complex, like some mispellings, abbreviations and regional differences (center/centre) you could build a pick(wildmatch()) expression to remap the names. You can build the expression by hand or build it using table input as shown in this attachment from the QV Cookbook. An expression like "fx*sup*" would match to "FX Support", "fxsupport" and "fx sup.".

As a final suggestion, you could build a regularizing function in VB using an algorithm like soundex. Call the function in the script and then map that result.

-Rob