topic List manipulation help needed! in QlikView

List manipulation help needed!

Tue, 12 May 2009 15:45:35 GMT

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,

List manipulation help needed!

Tue, 12 May 2009 17:10:31 GMT

Hi,

Add the following expression to the script:

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

Regards, Sander

List manipulation help needed!

Tue, 12 May 2009 17:29:24 GMT

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?

List manipulation help needed!

Tue, 12 May 2009 17:57:44 GMT

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

List manipulation help needed!

Tue, 12 May 2009 18:26:42 GMT

Hi,

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

Regards,

Ravi.....

List manipulation help needed!

rwunderlich — Wed, 13 May 2009 03:26:07 GMT

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