How to validate the scenario in description?

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.

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!