topic Re: Can we have multiple parameters in Replace function ? in QlikView

Can we have multiple parameters in Replace function ?

taha_mansoor — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

Can we use the replace parameters multiple time on Replace function.

I have the Materials table as follows

Material Code	Colour
1	LightBlue
2	JetBlack
3	DarkRed

and I want to replace the Light, Jet and Dark characters with L., J. and D. respectively but replace I guess does not allow to specify multiple conditions at the same time. Is there any work around for that ?

I want to acheive the above mentioned table as follows:

Material Code	Colour
1	L.Blue
2	J.Black
3	D.Red

I am wondering whether subfield could make it possible.

Regards,

Taha

Re: Can we have multiple parameters in Replace function ?

sunny_talwar — Wed, 20 Apr 2016 15:36:45 GMT

I think you are better of using MapSubString here:

Mapping:

Mapping

LOAD * Inline [

Field1, Field2

Light, L.

Jet, J.

Dark, D.

];

Table:

LOAD *,

MapSubString('Mapping', Colour) as NewColor;

LOAD * Inline [

Material Code, Colour

1, LightBlue

2, JetBlack

3, DarkRed

];

Re: Can we have multiple parameters in Replace function ?

taha_mansoor — Thu, 21 Apr 2016 07:20:48 GMT

Thanks Sunny, that could be one solution to this however, I need to make it dynamic so that it makes the prefix it self like L. J. D. and so on. do you have any idea on this ?

Re: Can we have multiple parameters in Replace function ?

sunny_talwar — Thu, 21 Apr 2016 15:12:08 GMT

How would you decide where is the break between the two words? is there a logic there?

Re: Can we have multiple parameters in Replace function ?

Anonymous — Sun, 26 Feb 2017 10:10:41 GMT

Hi Muhammad,

Not sure if it's still relevant...

you can use the Replace function omn specific field

example: (in your load statement)

Replace(columnX, 'Light', 'L.') as Color

In you case you wish to make a multiple replaces (3 as I read above), so just nest it together:

Replace(Replace(columnX, 'Light', 'L.'), 'Jet', 'J.') as Color

add more nested Replace function as you like

Re: Can we have multiple parameters in Replace function ?

marcus_sommer — Sun, 26 Feb 2017 11:46:31 GMT

With a slight tweak on Sunny's suggestion it should be dynamic:

Mapping:

Mapping

LOAD Field1, left(Field1, 1) as Field2 Inline [

Field1, Field2

Light, L.

Jet, J.

Dark, D.

];

- Marcus

Re: Can we have multiple parameters in Replace function ?

dey_joybroto — Fri, 22 Mar 2019 11:51:26 GMT

Nesting REPLACE with IF & ELSE would be helpful:

If(Colour = 'LightBlue', Replace(Colour, 'LightBlue', 'L.Blue'),
If(Colour = 'JetBlack', Replace(Colour, 'JetBlack', 'J.Black'),
If(Colour = 'DarkRed', Replace(Colour, 'DarkRed', 'D.Red'), 'N/A')))) as Colour