Hi,
I have a list of customers for which I'd like to check if the postcodes are valid based on a regular expression. The only thing is that my list of customers contains customers from all over Europe.
I started of by using the validator component to validate the pattern of the postcode with a regular expression as a pattern. Initialy I used the IF-THEN-ELSE part in which the IF part consits of an expression where I'm checking if the country field (string field) equals a specific value like "NL" this way I can filter on a specific value and then in the THEN part I added the regular expression of the common postcode for NL. However, the IF part doesn't filter on only the country value NL, but instead selects all the values for countries in the field $in.0.COUNTRY and then checks on the pattern in the then part.
I've also tried to use only a expression rule and in that rule use the function iif( $in.0.COUNTRY == "NL" AND $in.0.POSTCODE == "^[1-9][0-9]{3} ?[A-Z]{2}\s*", TRUE, FALSE), but this unfortunatly also doesn't select only the country codes with NL.
Is there a way to build a postcode check that is country specific using the two input field $in.0.COUNTRY and $in.0.POSTCODE?
-
Hi Anouk,
In this use case, the Validator may be a less favorable way to achieve your desired functionality. The Validator is more useful in complex multi-conditional situations.
Let me suggest to you a Simple Lookup Table.
For instance, you can create a lookup table that can be used to check/validate the country and the postcode in combination with a LookupJoin.
Let’s presume that you have the correct values defined in a validation file as a list of country codes and regexes.
You can create the Simple Lookup:
- Set the validation_file as the Data source.
- Set countryCode as the Key.
Keep in mind that you need to create metadata for the lookup, you can create those manually or you can use a FlatFileReader to extract them from the Validation file.
Now in the LookupJoin component:
- Set the countryCode as the Join key.
- simpleLookup0 as the Lookup table.
There are more ways to process the data, here is one of them.
In Transform:
- Create new Metadata with ClientCountryCode, ClientID, and regexValid (note Boolean).
- Connect the ClientID and ClientCountryCode with your source data that require validation.
- The regexValid is defined with matches a function (documentation about matches here). This function returns true if the ClientPostcode matches the regex returned by the lookup table.
Now if you run the graph, all the Valid client country codes will be sent to output port 0 of the LookupJoin and all Invalid client country codes will be sent to the output port 1 with ClientIDs so you can identify them.I added a Filter component with Filter expression:
//#CTL2 $in.0.regexValid == true;Using the same logic as the LookupJoin, the filter will divide the records by the regexValid being true or false.
Please sign in to leave a comment.
Comments 1