Validation: Formulae to validate incoming value format in a field?

Barcl · May 7, 2013 7:11AM

Hi - I need to check that a field contains only values formatted in a given way. For example we have a field which should contains values with the following format:
byte 1 should be alphabetic
bytes 2-5 should be numeric
byte 6 should be alphabetic
bytes 7-10 should be numeric

In order to verify that format I created a new field with the following formula:

IF ([Codice Monitoraggio]=null,null,
LEN([Codice Monitoraggio])!=10,"lunghezza errata",
(LEFT([Codice Monitoraggio],1))<"A","Formato errato",<br /> (LEFT([Codice Monitoraggio],1))>"Z","Formato errato",
(MID([Codice Monitoraggio], 2, 4))<"0000","Formato errato",<br /> (MID([Codice Monitoraggio], 2, 4))>"9999","Formato errato",
(MID([Codice Monitoraggio], 6,1))<"A","Formato errato",<br /> (MID([Codice Monitoraggio], 6,1))>"Z","Formato errato",
(MID([Codice Monitoraggio], 7, 4))<"0000","Formato errato",<br /> (MID([Codice Monitoraggio], 7, 4))>"9999","Formato errato",
null
)

I was wandering if I can find a better way to verify those values.

Do you believe it is possible to use a JavaScript based on Regular Expressions (RegEx) with the match function? I'm not a Javascript expert... I was trying to find a PHP ereg equivalent in Java?

Thanks.

Barcl · May 7, 2013 10:36AM

As far as I know standard format evaluation in Omniscope send an alert that one or more value in an field are not as expected and eventually prevent Omniscope to continue processing. The true intent of this evaluation is to allow Omniscope to discover any unexpected format changes in an input file (or table).

I already know that an application contain uncontrolled data in some tables/rows/fields and I would like to identify wrong info in order to supply a list of unformatted data to the application owner to update that info.

What I need is a formula that allow me to filter wrong records to fix.

Thanks for your quick answer

paola · May 8, 2013 6:02AM

You should try to use the Validate Data operations block, in the DataManager space (can be used to validate information at the beginning of process, before it enters Omniscope block, or on its way out, to validate the report results). It will enable you to set multiple validation rules, with different outcomes (some will result in just warning, some data will not be allowed to pass through the block). By using Rules/ Schema or Custom modes, you will be able to validate field values, data type, or set a custom formula, to perform calculations on the data.
You can see how to use this block by watching one of our tutorial videos:
http://tc.visokio.com/videos/?name=DataManagerValidateData&title=Validate+data&lang=gb

Barcl · May 9, 2013 4:40AM

Thank you for you suggestion Paola, I watched the video an the result is that it doesn't help to solve the problem. In schema validation is it possible to validate the format of the field (text, number and date only).But this is not enough for us.

We need to validate field's content against the following format:
Byte 1 must be an uppercase letter (A-Z)
Byte 2-5 must be numeric (0000-9999)
Byte 6 must be an uppercase letter (A-Z)
Byte 7-10 must be numeric (0000-9999)

So we need to use a formula in custom validation. In my opinion using Regex would be the best solution? And we need many validation like this in other fields

Could you please help me to do this?

tjbate · May 9, 2013 6:05AM

Claudio - Here is a possible RegEx solution:

DECLARE(code, 

REPLACEREGEX(
[Field 1], "bytes? ([0-9|\\-]*).*", "$1", true), 
IF  (OR(code = "1", code = "6"), "alphabetic", 
OR(code = "2-5", code = "7-10"),"numeric", null))

Unfortunately, the option to use a formula like yours or this one to validate just one specific field in the passing data set is not currently available from within the Validate Data block. Instead, for now you must use an 'upstream' Field Organiser block to apply validation formula like the above that return specific values for failed records, e.g. "Formato errato". You can then use the Validate Data and/or Record Filter blocks to test for this specific value and further test/reject/channel sets of failed records.

Welcome!

Categories

Ideas Parade

Tagged