Could you clarify the context? Is it about an arbitrary dataset/table where columns with the countires and cities are unknown and there should be a way to:

1. identify country column;
2. identify city column;
3. for each city in city column check if it exists in the corresponding country that is in country column?

I am able to detect the country column but not able to validate the above condition.

If country and city columns are known then the condition should rely on additional lookup file (should be prepared in advance):

cities.lkp (example)

Using this lookup it should be possible to detect in what country city is and compare it to the value of country column

I am not sure if there is a function in expression language one can use in simple validation rules of Ataccama One Web interface, but it should be doable in a component validation rule (using Ataccama IDE)

Please provide me the rule.

Unfortunately I don’t have any environment (if you need the rule for the Ataccama One web) where I can try to implement proof of concept of the component validation rule. I believe component based validation rules are covered by one of existing trainings ataccama has.

If we speak about general approach, using DQC (Ataccama IDE) you’ll have to do following:

Build lookup file for the mapping of city to country

Excel file reader is for 

Lookup builder

Test example data in another excel

countries_dataset reads excel file with “data”:

Check the data with lookup we’ve built in prev step:

Getting city_country from the lookup (mapping of the city to the country):

With the column assigner we have to validate if country we get from dataset is equal to the actual country we get from lookup mapping:

And the result is in text file:

Thank you.  I have created lookup file like the below: 

I have used the below rule:

It did not work.

Then I created 2 look up files:

THen Rule:

It did not work.

Please suggest something in Web interface.

Actually what you can do is to have something like this:

The steps are:

1. build a lookup with cities and countries joined with “_”:

   

2. upload to Ataccama One

   

3. Create a validation rule with 2 input parameters

   

4. Select Advanced Expression as a rule logic and check if concatenated CITY + “_” + COUNTRY is in the lookup

   

PS

It would be nice to build a lookup with all Uppercased values and use upper cased city and country in the rule not isInFile(upper(CITY + "_" + COUNTRY), "lookup:city_country").

Thanks a lot but while creating look up file it is removing ‘_’.

CSV:

LKP:

I am able to create look up file 

But the above rule is not working

Its working thanks a lot

This is great!