Complex package with multiple transformations and validations

Hello,

I have a "complex" question and since I am new to AETL, I would like to get some help to see if what I am trying to do is possible.

1. I am trying to load very scrambled data and fix this data by doing multiple transformations and validations
2. The first step is load an excel file into AETL with over 11.000 records. The excel-file contains a column called LANDV and this has a lot of bad data (wrong spelled countries, dots, various combinations of city and country etc).
3. I was able to first make a transformation with the lookup-transformation and with this, I am cleaning up this column and the end-result is having these records with bad data updated with new better data
4. The result is that i then have 11.000 records with multiple countries in there, of one being the value Duitsland (=Germany).
5. Now, as a second step, I want to do a validation on the postcode of these specific german records
6. However, I don't know how I can only take the records where column LANDV="Duitsland" and then check the other column POSTKV (being the postcode) to see if for these records, the values are valid German postcodes. The rest of the records should not be validated and passed through
7. The next step is some additioanl transformations
8. Then last write the result to a SQL Server table
9. ALSO, I would like to have output (to a table or log file) of the rejected records, so the users could use this as input to update the source excel-sheet

Could somebody guide me specifically to point 6, because I don't know how to evaluate only the records where LANDV="Duitsland" and ignore the other recortds, but pass these other records on the the next step anyway. So only records for germany with an invalid postcode should be skipped.

Any potential help on this?

regards,
Norman

You can redirect the records into the different flows using a validator.
Then do the validations and merge it back using the union

Validators have two outputs success and failure

/wiki/advanced-etl-processor/transforming-data/validator/

"how I can only take the records where column LANDV="Duitsland""

Please use Is Equal to the validation function

Hi Mike,

Again, bear in mind I am new to this tool; I am getting stuck when I do the first validation. I understand that I can do a validation and that I can process the 11.000 records checking if the country is equal to "Duitsland". But the out come is success or failure of that validation, and I don't know the next step.

It validates the outcome, but then what? Where is this outcome stored? I have to do 2 paths:
1. if the country = Duitsland, then after that I need another validation after that to see if the postcode is correct. All records with incorrect postcodes should be written away so the user can update these records. The correct records should be passed through
2. if the country <> Duitsland, then the records should be passed through (I need validators on these records as well, but for the sake of this test I want them simply to be passed through

Once the 2 validations have been executed (first on specific country, second on valid postcode for this specific country), I should have these valid records merged again into output (that also needs a transformation afterwards, but that I know how this works) and then written to the writer.

I am stuk on how to process the data within/after the first validation on country, I don't know how to process this in the tool. I can only do success or failure in that validation, so if the country is equal to Duitsland, what happens with the records that <> to Duitsland?

If you could point me with clear directions on how to do this, this would be very helpfull.

regards,
Norman

Hi Mike,

first of all, thanks a lot for the assistance this morning. It was very helpfull.

Brings me to another question: as shown, I first update the country column with valid countries, and then I check which of the 11.000 records have the country Germany, and then another validor to check the post code. All the good records are joined in an union all.

But if i want to validate e.g. the UK as a country after the first transform step (where i make each country a valid entry), I can do the same trick. BUT I can't add the correct records to the Union All- step as it only has two inputs.

How can I make x-many validations and store are the good records in a (temp) place to finally write them away?

Any ideas?

Regards,
Norman

There are multiple ways of doing it

You can do something like this.

(You can copy and paste validators and unions)

Or you can use case transformation.