Replace, Field with more than 100 values

[rvalenzuela]

Hi,

I'm new to Qlik and I have a rookie problem.

I need to replace some values of a field, the obvious answer is to replace it on the Data Manager, but my Field has more than 100 values.

Is there any code I can use to do it when I load the original data?

Thanks

[JustinDallas]

What do you mean by "replace" and "100 values"?  Can you give us an example?

You might be able to do something like this if you are just removing a bad value.

MyTable:
LOAD *,
If( SomeValue = 'Bad', 'Now Good', SomeValue ) AS 'CleanedValue'
LOAD * Inline
[
     'OrderId', 'SomeValue'
       1, 'Bad'
        2, 'Good'
       3, 'Good'
]
;

[sunny_talwar]

May be using ApplyMap and Mapping Load

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...

[rvalenzuela]

Hi Justin,

I have a field with 179 names, some names are for the same people so for example:

John L

J Lennon

John Winston Ono Lenon

John W. Lennon

All those names I'd like to have them as 'John Lennon'. The ideal way is to go to the Data Editor and use the replace function, but as I have more than 100 names I can't do that.

Hope I'm clear this time....

[JustinDallas]

How do you know that all those names map to the same person?

[petter]

You can still use the techniques that Sunny and Justin told you about. You just have to repeat the to-name for each of the from-names in the mapping table (translation table).

[balabhaskarqlik]

May be like this:

If(Match(Field,'John L','J Lennon','John Winston Ono Lenon','John W. Lennon'),'John Lennon',

If(Match(Field,'John R','J Rennon','John Winston Ono Renon','John W. Rennon'),'John Rennon', 'ABC'))

By using of Match(), MixMatch() functions.

Because one name is having 3 or 4 similar names.

[rvalenzuela]

Thanks, it worked!

[rvalenzuela]

Finally used @Bala's answer.

If(Match(Field,'John L','J Lennon','John Winston Ono Lenon','John W. Lennon'),'John Lennon',Field)

Only repeated the field at the end so the rest of the values (the ones not changed) remain as the original Field.

Cheers!

[balabhaskarqlik]

Ohh, i'm glad, my ans solved your issue.

Please make it as Answered & close the thread, Thank you.