Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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