Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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
];
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 ?
How would you decide where is the break between the two words? is there a logic there?
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
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
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