Conditional split on multiple assert streams having error not working as expected

Question

I have a data flow that checks for various conditions part of data quality check. I have four assert transformations that do this job. The conditions for the assert are passed dynamically from a database table.

ColumnName	DefaultValue	MaxCharLength
City	Tokyo	20
Business	Car	null

Dynamically generating the assert conditions using SQL and passing into a data flow parameter.

I have four assert transformation to check each conditions, one to check default value, one for MaxCharLength and likewise.

toString(byName('City')) == 'Tokyo'
toString(byName('Business')) == 'Car'

length(toString(byName('City'))) <= 20 

now since MaxCharLength for Business column is null, I pass true() into the assert transformation expecting the assert transformation to validate it has true and not show any error. Whether to pass the expression or true() is handled using the SQL statement.

All these are working fine but when I use a conditional split to get 'Error Data' and 'Good Data' I face issue.

The expectation is

• if the data has at least one assert having error, it is treated as bad data. Entire data will be written to Reject folder as a csv file.
• If data has no error at all, then this good data will be written to Accept folder.

Below is the expression I'm using for both streams -

//Error Data
not(hasError('Assert Stream01') == true()) && not(hasError('Assert Stream02') == true()) && not(hasError('Assert Stream03') == true()) && not(hasError('Assert Stream04') == true())

//Good Data
hasError('AssertStream01') == true() || hasError('AssertStream02') == true() || hasError('AssertStream03') == true() || hasError('AssertStream04') == true()

I have tried tweaking the expression few times but am not getting right data into the stream. Error data shows up in 'Good Data' stream or sometimes both streams show error data.

How to fix this pls? Need help with correcting the expression.

Thanks in advance.

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

Alright, you are almost there, but your conditional split expressions are wrongly assigned between Error Data and Good Data branches. You have swapped the logic, and that is why you are seeing error records in both branches.

Let me correct it :

First, you hae to understand:

hasError('AssertStream01') returns true if there is an error in that Assert transformation.

You want bad data to go to "Reject" if any one assert has an error.

You want good data to go to "Accept" only if no asserts have any error.

Corrected one:

For 'Error Data' (Reject folder): If any assert has error, send it to Reject folder.

hasError('AssertStream01') == true() || hasError('AssertStream02') == true() || hasError('AssertStream03') == true() || hasError('AssertStream04') == true()

Why your current logic fails:

• You used not(hasError()) for Error Data and hasError() for Good Data.
• That is completely reversed.
• Also, small but important, avoid extra == true() checks. hasError() already returns a boolean. No need to check == true().

Note:

In your conditional split, order matters.

Always put the Error Data rule first, then Good Data.

Because otherwise it may match wrong branch.

So, in the Conditional Split UI:

1. First rule: If Error Data condition is true -> Go to "Reject"
2. Second rule: Else If Good Data condition is true -> Go to "Accept"

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.

Answer 2

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer. 

Solution:

though that didn't help. I am using the below expression and all the data is getting showed in valid data whereas it should show in error data.

For error data: hasError('assertStream01') || hasError('assertStream02') || hasError('assertStream03') || hasError('assertStream04')

For good data: !hasError('assertStream01') && !hasError('assertStream02') && !hasError('assertStream03') && !hasError('assertStream04')

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information. 

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue. 

 

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.