Can we have multiple parameters in Replace function ?

[taha_mansoor]

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

[sunny_talwar · Accepted solution]

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]

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 ?

[sunny_talwar]

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

[Anonymous]

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

[marcus_sommer]

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]

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