topic Validate fields formats to find exceptions in QlikView

Validate fields formats to find exceptions

Fri, 25 May 2012 13:10:59 GMT

Hi,

I have data fields that have been input and want to try and make an exception report that will pull any fields that are not part of a specific format.

The format that each field should be will be 2xletters followed by 6xnumbers. LLNNNNNN.

Each field should be 8 chars long.

The 2 letters should be only certain combinations, eg. AA, AB but not LL, LS, etc.

Please can you explain the best way of doing this, is it as an expression in a chart?

Thanks

Validate fields formats to find exceptions

vgutkovsky — Fri, 25 May 2012 15:32:47 GMT

This would be a script thing preferably, not UI. For the letters, first create a mapping table of valid 2 letter combinations. The 2nd field of the mapping table would be just a 1. For example:

letter_combo_map:

MAPPING LOAD * INLINE [

combo,valid

AA,1

AB,1

];

Instead of inline you can also do this from any data source you like (e.g. Excel). Assuming your field is called "myField" the load would then look as follows:

LOAD

myField,

fabs(applymap('letter_combo_map',left(myField,2),0) * isnum(num#(right(myField,6)))) as field_valid

... //specify your data source, etc

The new field "field_valid" will be 1 if it matches the format, otherwise 0.

Regards,

Vlad

Validate fields formats to find exceptions

erichshiino — Fri, 25 May 2012 20:52:33 GMT

Hi, I am not sure how well this will fit you problem. but you should take a look at this:

http://community.qlik.com/qlikviews/1024

Regards,

Erich