Nesting IF and REPLACE to clean in data load

[RWilliams92]

Hi everyone,

I'm having trouble creating an IF statement that cleans and pulls through the correct text from a string.

I have 2x types of strings in my data source, see below;

LOC-10000000.1

ORD-10000000.4

The cleaned versions will be ORD-XXXXXXXX (so ignoring any additional characters after the 8 numbers AND replacing LOC with ORD). This is so I can then match up to another data table.

I currently have;

LOAD "CCT_THIRDPARTY",
If(Index("CCT_THIRDPARTY",'ORD-',1)=1,
Mid("CCT_THIRDPARTY",Index("CCT_THIRDPARTY",'ORD-',1),12))      as Clean_CCT

How would I go about adding REPLACE("CCT_THIRDPARTY", 'LOC', 'ORD') to this statement?

Thanks in advance

[felipedl · Accepted solution]

Hi,

Use it like this:

load
CCT_THIRDPARTY,
If
(
Index(REPLACE("CCT_THIRDPARTY", 'LOC', 'ORD'),'ORD-',1)=1,
Mid(REPLACE("CCT_THIRDPARTY", 'LOC', 'ORD'),Index(REPLACE("CCT_THIRDPARTY", 'LOC', 'ORD'),'ORD-',1),12)
) as Clean_CCT;
load * Inline
[
CCT_THIRDPARTY
LOC-10000000.1
ORD-10000000.4
];

this will change any LOC to ord and then apply your logic.

Hope it helps.

[RWilliams92]

Perfect! Thank you that worked a treat.