"Negative" rules in Rule based row splitter possible

Hi All,

starting to tweak my databases of vendor data I hit a roadblock.

I use various rule based row splitters to extract/group my vendors in Europe based on the Europen VAT number data-works great :-)

I have a data structure which (next to others) has the columns ..

"Country" (2 digit ISO code-e.g. GB)

and "VAT Reg No" (ISO code+number, e.g. GB123456789)

String used there is e.g.

"$VAT Reg. No.$  LIKE  /GB*/ => TRUE

Rows with GB VAT number go into Table 1, rest data in table 2 for further splitting.

I have done this for 20+ european countries by country.

I now also want to capture the ones where we miss the European VAT number but shall have one (as the vendor is in a European country with mandatory VAT no).

So e.g. when the ISO code is "DE" in the "Country" Column, but the "VAT Reg number" is a blank or crap data rather than a VAT no this shall go into table 1.

Is there a way to implement a "negative rule", like  MATCHES NOT or NOT LIKE.

The above as condition is necessary, as I have just about 25 types of VAt nos (ISO & *), but many many more variants of nonsense entries-so cant split by normal "positive" rule.

Want to achieve smth which says

 "$country$ LIKE (/AT/,/BE/,/CY/,/CZ/,/DE/..etc.)."

 AND

VAT number NOT LIKE "AT*" OR "BE *" OR "CY" OR"CZ" OR "DE" ..etc => TRUE

Tried to achieve this with various rule tries containing LIKE, IN and so on, but no luck

 

Sample rule used:

$Country$ IN (/AT/,/BE/,/BG/,/HR/,/CY/,/CZ/,/DK/,/EE/,/FI/,/FR/,/DE/,/EL/,/HU/,/IE/,/IT/,/LV/,/LT/,/LT/,/LU/,/MT/,/NL/,/PL/,/PT/,/RO/,/SK/,/Sl/,/ES/,/SE/,/GB/) AND $VAT Reg. No.$  NOT LIKE/AT/,/BE/,..... => TRUE

So if the "country" is a country with a European VAT number but there is no valid VAT number starting with ISO code and wild card, these rows shall go into the first table of the rule based row splitter (so the VAT no can be obtained when cleansing the data).

Hope it is clear what is my problem?!

Thanks a lot for any help.

Jürgen

 

 

 

 

 

Instead of

vat number NOT LIKE 

you want

NOT (vat number LIKE)

eg: ($Country$ IN (/AT/) AND NOT ($VAT Reg. No.$ LIKE "AT*") => TRUE

Next, to match the LIKE pattern with the country code for the data row you are looking at, use a String Manipulation node or a String Replacer node to create AT* from AT, and use it in the rule spllitter as

($Country$ IN (/AT/,/BE/,/BG/,/HR/,/CY/,/CZ/,/DK/,/EE/,/FI/,/FR/,/DE/,/EL/,/HU/,/IE/,/IT/,/LV/,/LT/,/LT/,/LU/) AND NOT ($VAT Reg. No.$ LIKE $Country VAT pattern$") => TRUE

$Country VAT pattern$ is a column created by appending "*" to the two letter ISO country code.

 

David