× Data Warehousing and Data integration

Validation Object - Is Equal To (Rejecting Records Erroneously)

  • Posts: 201
  • Thank you received: 1

Validation Object - Is Equal To (Rejecting Records Erroneously) was created by imchaz

In my validation Editor, I have an Is Equal To Transformation action.

I have 3 descriptive words from a record that could potentially pass into it.
1.INTERNET
2.WAN
3. NON-RECURRING

My problem on random records, even though I am passing the word INTERNET into it, it would still reject it.
I have no idea why this is happening. I have isolated the records this happens to but I am not sure on how to properly debug it.

What will you need from me so you can replicate this? I have a Database, Excel Files that I can share with you privately.
As you can see in the Data Transformation Log, it sees it as INTERNET but its still rejecting.

Thanks,
4 weeks 23 hours ago #21023
Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 8212
  • Karma: 33
  • Thank you received: 528

Replied by admin on topic Validation Object - Is Equal To (Rejecting Records Erroneously)

Hello

You might have spaces at the end which you cannot see.
Use Trim function to remove them

I think in your case "Is In list" validation function is more appropriate.
www.etl-tools.com/wiki/aetle/validation_rules/miscellaneous
(Use entered manually as source)
Mike
4 weeks 21 hours ago #21025

Please Log in or Create an account to join the conversation.

  • Posts: 201
  • Thank you received: 1

Replied by imchaz on topic Validation Object - Is Equal To (Rejecting Records Erroneously)

I am not sure why my previous response was badly formatted so here it is again.

Hi Mike, that did not resolve the issue. So I went back and poked around on something about SQL Lookup Objects. I have found the culprit and was looking in the wrong area. The log files was giving me a false error message.
Let me describe my findings.

See Transformation_Pre.PNG
Here is the initial result of running an Excel File through the transformation. See the highlighted items. It found 288 good records and 18 bad records. In reality all 306 records should have been good. When I reviewed the log file, you will see 18 records referring to the IS EQUAL TO object. The IS EQUAL object setup is if it sees the word NON-RECURRING, reject the record.
See the file and screenshot of the log file called DataTransformation.log and DataTransformation.PNG

See the file Validation_SetUp.PNG
Here you will see the initial setup. The important parts are the items circled in yellow.

See Validation_GoodRecord.PNG
As I cycle through the records, at record 23, you will see I am passing the word INTERNET and the SQL Lookup returns a value of 164378

SeeValidation_Lookup_SourceTab.PNG
The SQL lookup basically returns the Top 1 record from a table using the parameters passed.

See Validation_BadRecord.PNG
Now here is where I found the issue of the record being rejected. As you can see for record 24, it finds the word INTERNET but in the SQL LOOKUP, it still returns the value of 164378. But in reality, it should not find a record, and as you can see there is an RED X icon in the top right corner.

HERE IS WHERE I FOUND THE CULPRIT
See Validation_Lookup_ErrorHandling.PNG
In the SQL Lookup, the Error Handling tab, nothing is set. I am not sure during an upgrade process from a previous AETL Pro version, something changed for this to set the option to nothing or maybe that is what it always was. But prior to my upgrade this was working correctly.

See Validation_Lookup_ErrorHandling_SetToNull.PNG
Here I am setting the option SET TO NULL

See Transformation_Post.PNG
When I set the SQL LOOKUP properly, you will see the items in yellow properly processing all 306 records. Do you think you can properly identify the problem in the logs in your next version upgrade? I wasted so much time finding this bug.I hope this will help someone in the future.

Thanks,
Chuck
3 weeks 6 days ago #21035
Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 8212
  • Karma: 33
  • Thank you received: 528

Replied by admin on topic Validation Object - Is Equal To (Rejecting Records Erroneously)

Hi Chuck

Thank you for providing so much information.
It does help a lot
We will add the fix to the next release
Mike
3 weeks 6 days ago #21038

Please Log in or Create an account to join the conversation.

  • Posts: 1153
  • Karma: 3
  • Thank you received: 141

Replied by Peter.Jonson on topic Validation Object - Is Equal To (Rejecting Records Erroneously)

Hi Chuck

The problem you reported was corrected in the latest release.

The issue was when a new lookup is created default error handling parameters were not assigned

Peter Jonson
Support Analist
The following user(s) said Thank You: imchaz
2 weeks 21 hours ago #21060
Attachments:

Please Log in or Create an account to join the conversation.

 

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies