Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Nesting IF and REPLACE to clean in data load

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

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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.

View solution in original post

2 Replies
Highlighted
Partner
Partner

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.

View solution in original post

Highlighted
Contributor II
Contributor II

Perfect! Thank you that worked a treat.