LOAD script to filter out field values beginning with a number

[mattphillip]

I'm hoping someone might be able to help. We're looking to improve the data quality of our postcode field within our apps. The UK format for a valid postcode begins with a letter. However, as the UCAS field (where students submit their applications is a free text field) resulting in some invalid values being entered, including those beginning with a number.

I'm just wondering if there is a function I could use within the QlikSense load script to check whether the field value begins with a number, and if so, replaces it with a blank value?

Any help would be most appreciated.

Yours,

Matt

[BrunPierre]

Hi @mattphillip 

Perhaps like this?

LOAD *,

If(IsNum(Left(FieldName,1)) = -1,'[blank value]'&Mid(FieldName,2) ,FieldName) as NewFieldName;

LOAD * Inline [
FieldName
1QWERT
9KH6Y
19846
PIUTE
M1374B
];

DROP Field FieldName;
RENAME Field NewFieldName to FieldName;

EXIT SCRIPT;

[mattphillip]

Thanks that worked a treat. I do have a follow up question if that's ok. It appears that some of the numerical values in our dataset are stored as text. Any ideas how to approach filtering those out?

Thanks in advance.

Matt

[BrunPierre · Accepted solution]

Wrap in the Num() function for numeric interpretation.

i.e.

Num(Field) as Field