topic Validating Data in a load script in QlikView

Validating Data in a load script

Thu, 10 Jun 2010 15:49:44 GMT

Hi,

I'm trying to write a load script that only loads data from a field where it is in the correct format - 'XX## #XX' i.e. UK postcode. I have introduced a validation rule using the LEN function but how would I go about ensuring all match the exact format?

Thanks,

Validating Data in a load script

Oleg_Troyansky — Thu, 10 Jun 2010 16:04:17 GMT

I don't think there is a function that validates format pattern... You might have to parse the string and validate each part separately... Search the Help Text for "String Functions" - there are quite many, and they are all described in one article.

Validating Data in a load script

Thu, 10 Jun 2010 16:08:25 GMT

thanks for letting me know.

Validating Data in a load script

Thu, 10 Jun 2010 16:18:22 GMT

Just thinking this one through, even if I parsed the string (e.g. left(postcode, 2)) how would I validate the part?

Validating Data in a load script

Oleg_Troyansky — Thu, 10 Jun 2010 18:13:26 GMT

That's why I mentioned String functions - there is a variety of ways to compare strings and substrings and ensure that vertain part is noly numeric, and certain part is only characters - Index, Match, FindOneOf, and more... You just need to formulate your conditions properly. Worst case - you might have to validate each one of the 8 characters, if it's so important...

Validating Data in a load script

johnw — Sat, 12 Jun 2010 00:52:30 GMT

I'm honestly not thinking of a better way than pure brute force:

if( index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,1,1))
and index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,2,1))
and index('0123456789' ,mid(postcode,3,1))
and index('0123456789' ,mid(postcode,4,1))
and mid(postcode,5,1) = ' '
and index('0123456789' ,mid(postcode,6,1))
and index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,7,1))
and index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,8,1)),postcode,'Error') as postcode

I mean, we could create fake functions, but I don't think it's worth the complexity. I think it would look something like this:

SET alpha = index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid($1,$2,1));
SET digit = index('0123456789',mid($1,$2,1));
SET space = mid($1,$2,1) = ' ';

if( $(alpha(postcode,1))
and $(alpha(postcode,2))
and $(digit(postcode,3))
and $(digit(postcode,4))
and $(space(postcode,5))
and $(digit(postcode,6))
and $(alpha(postcode,7))
and $(alpha(postcode,8)),postcode,'Error') as postcode

It won't surprise me if there's a clever way to do it. I'm just not thinking of one.