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