Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
taha_mansoor
New Contributor III

Can we have multiple parameters in Replace function ?

Hi all,

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

I have the Materials table as follows

  

Material CodeColour
1LightBlue
2JetBlack
3DarkRed

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 CodeColour
1L.Blue
2J.Black
3D.Red

I am wondering whether subfield could make it possible.

Regards,

Taha

Tags (2)
1 Solution

Accepted Solutions

Re: Can we have multiple parameters in Replace function ?

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

];

6 Replies

Re: Can we have multiple parameters in Replace function ?

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

];

taha_mansoor
New Contributor III

Re: Can we have multiple parameters in Replace function ?

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 ?

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

nirkatz123
Contributor

Re: Can we have multiple parameters in Replace function ?

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

MVP & Luminary
MVP & Luminary

Re: Can we have multiple parameters in Replace function ?

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

dey_joybroto
New Contributor

Re: Can we have multiple parameters in Replace function ?

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