Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
rvalenzuela
New Contributor

Replace, Field with more than 100 values

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

9 Replies
JustinDallas
Valued Contributor II

Re: Replace, Field with more than 100 values

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'

]

;

Re: Replace, Field with more than 100 values

rvalenzuela
New Contributor

Re: Replace, Field with more than 100 values

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
Valued Contributor II

Re: Replace, Field with more than 100 values

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

MVP
MVP

Re: Replace, Field with more than 100 values

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
Honored Contributor

Re: Replace, Field with more than 100 values

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
New Contributor

Re: Replace, Field with more than 100 values

Thanks, it worked!

rvalenzuela
New Contributor

Re: Replace, Field with more than 100 values

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
Honored Contributor

Re: Replace, Field with more than 100 values

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

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