topic Re: Nesting IF and REPLACE to clean in data load in Visualization and Usability

Nesting IF and REPLACE to clean in data load

RWilliams92 — Fri, 24 May 2019 15:03:26 GMT

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

Re: Nesting IF and REPLACE to clean in data load

felipedl — Mon, 27 May 2019 13:11:51 GMT

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.

Re: Nesting IF and REPLACE to clean in data load

RWilliams92 — Wed, 29 May 2019 09:46:54 GMT

Perfect! Thank you that worked a treat.