Search a Text in a String & replace it with Blanks

[SK28]

Hi,

I have a Free text field in qliksense

Data - configuration and performance  of Integration with Qliksense, we will do first

Data in Qliksense table dislaying as - configuration and performance{color:#4c9aff} of Integration{color:#4cff} with Qliksense, We will do first{color}

Expected result - configuration and performance  of Integration with Qliksense, we will do first

I want to search for these kind of  {color:#4c9aff} & {color:#4cff} & {color} color code in my description field and replace it with blanks.

[justISO]

Hi, maybe try something like this:

=replace (replace( replace([your_data_field], '{color:#4c9aff}', '') , '{color:#4cff}', ''), '{color}', '')

[SK28]

Yes but This is not dynamic right, Is it possible to search anything that has '{colo*' AND REPLACE IT WITH '' (Blanks)

If they have used any other color, then new color code {'color:45dff4f'} will show up right so, looking for dynamic solution

[Lakshminarayanan_J]

@SK28 

Kindly try on below expression ,

If(Wildmatch(FiledName,'colo*'),null(),FiledName) as [New Value],

[justISO]

Yes, it is not dynamic, but, if there are only 3 possible {...} text places in your data_field, you can replace any text between braces {} to blanks (and {} itself) with something like this:

=replace( replace( replace( replace([your_data_field] , TextBetween([your_data_field], '{', '}', 1), ''), TextBetween([your_data_field], '{', '}', 2), '') , TextBetween([your_data_field], '{', '}', 3), '') , '{}', '')

[SK28]

Data in Qliksense table dislaying as - configuration and performance{color:#4c9aff} of Integration{color:#4cff} with Qliksense, We will do first{color}

Expected result - configuration and performance  of Integration with Qliksense, we will do first

I just want to remove/replace these kinds of {color:#4c9aff} color codes with ''(blanks)

the above syntax will completely eliminates the data.

[SK28]

this is partially correct, But I want to do this dynamically anything which has '{color  in it

[justISO · Accepted solution]

just adjust previously provided expression with {color:

=replace( replace( replace( replace([your_data_field] , TextBetween([your_data_field], '{color', '}', 1), ''),
TextBetween([your_data_field], '{color', '}', 2), '') , TextBetween([your_data_field], '{color', '}', 3), '') , '{color}', '')

[SK28]

if there are morethan 3 then? we never know how many such possibilities are there? Can this be achieved?

[justISO]

I don't know any way how to know how many {color} possibilities are in the sentence. But you can add more textbetween() parts, as each are taking specific braces in text (it is defined in 3rd parameter of this formula), to be safe each {color} is removed.