_L and _M Table Behavior

  • 27 November 2020
  • 2 replies

I am using the Field Validation to test our ETL. Using this as source data:

And these rules in the DSA:

And this query to check for errors:

I get the following result:

The _L table is linking to the records in the _R table that were removed when they failed the validation step on FIRST_NAME. Also the Warning level notification is not present in the table.

As it stands the _L, _R, and _M tables as joined above are showing incorrect or at least confusing information. Am I using them as they were intended to be used, and has anyone done anything to get around this?




Best answer by JTreadwell 1 December 2020, 00:27

View original

2 replies

Userlevel 3
Badge +5

Hi Mark, 

Yes, that looks okay to me. 

The validation operators specifiy which data is VALID. So in your example: "Not in list ('Bat', 'Super')" means that Batman and Superman are VALID entries, therefore will not be flagged by the validation rule. Both the Error AND the Warning are flagging Ironman and because you're using an Inner join, you are only seeing one of the messages. 

Try swapping the logic on your validation rules and see if it gives you the desired results. 

You can also see the results by viewing the error/warning from the reports menu in TimeXtender.

You can read more about table validation rules here: Selecting, Validating and Transforming Data – TimeXtender Support

Thanks Joseph, helpful as always