Change format data value in load script

[KirstenKa]

I have this value in my dataset 

SAP.Trading Co

1011B034-003

And would like to transform this value in my load script to this value 

1011B034.003

[BrunPierre · Accepted solution]

Hi, if it's just to substitute hyphens (-) with decimals (.), then you should make use of the Replace ().

Replace("SAP.Trading Co", '-', '.')  as "SAP.Trading Co"

[KirstenKa]

Thanks @BrunPierre  that seems to work, though I also would like to join on this new value, but since the new value is added after the join it doesn't recognize the value

The join looks like this

C8:
LOAD
"Item Trading Code" & '|' & "Color Code" & '|' & "sku" as %ItemColor_GlobalId,
"Item Trading Code" & '|' & "Color Code"& '|' & "sku" as ItemColor_GlobalId,

Left Join( C8 )
SAP:

LOAD
"Item ID" & '|' & "Color" & '|' & "SAP.Trading Co1" as %ItemColor_GlobalId,
"Item ID" & '|' & "Color" & '|' & "SAP.Trading Co1" as ItemColor_GlobalId_SAP,
Article,
Replace("Trading Co", '-', '.') as "SAP.Trading Co1",

[BrunPierre]

If my understanding is correct, then this way;

C8:
LOAD
"Item Trading Code" & '|' & "Color Code" & '|' & "sku" as %ItemColor_GlobalId,
"Item Trading Code" & '|' & "Color Code"& '|' & "sku" as ItemColor_GlobalId,

Left Join(C8)
LOAD *,
"Item ID" & '|' & "Color" & '|' & "SAP.Trading Co1" as %ItemColor_GlobalId,
"Item ID" & '|' & "Color" & '|' & "SAP.Trading Co1" as ItemColor_GlobalId_SAP

SAP:
LOAD Article,
Replace("Trading Co", '-', '.') as "SAP.Trading Co1",
...

[KirstenKa · Accepted solution]

Thanks I placed the join after the load, that worked