Match Row data horizontally

[Saravanan13]

Hello,

I have extracted data from two sources which is saved as Intake and Tax in the attachment. I am looking validate the conditions in the Validation sheet and give a output as Reject or proceed.

Basically need to compare the rows in Intake sheet & Tax sheet according to the conditions in the validation sheet.

Can anyone assist on  this, Some of the field name will not be the same. Example version will be only in Tax form.

[geraldo]

@Saravanan13 

Here's a first idea to put together something you're thinking about. Through this idea we will be able to discuss what you really want with even more records to be able to control the logic

[Yoshiro_Fujimori]

Hi @Saravanan13 ,

1. As Alteryx is good at dealing with data in a row, when you get data of one observation in one column, it is better to make it to a row in the first place.

2. As your vaildation questions are all Yes or No, it would be convenient to use Bool type to store the answers.

Workflow

Expressions in Formula Tools

is_2023 = [Tax_Version] = "2021"

match_First_Name = 

GetWord([Name_of_the_Individual], 0)

= [Tax_First_Name]

match_Middle_Name = 

IF CountWords([Name_of_the_Individual]) > 2 THEN GetWord([Name_of_the_Individual], 1) ELSE Null() ENDIF

= [Tax_Middle_Name]

* As I am not sure if everyone has Middle name, I added IF statement as above.

match_Last_Name = 

GetWord([Name_of_the_Individual],CountWords([Name_of_the_Individual]) - 1)

= [Tax_Last_Name]

has_PO_Box = Contains([Tax_Address], "BOX", 1)

contains_US = Contains([Address], "US")

** You may need to define the criteria of "has PO Box" and "contains US" in more detail, as it is a bit fuzzy now.

Proceed = 

[is_2023] AND [match_First_Name] AND [match_Middle_Name] AND [match_Last_Name] 

AND ![has_PO_Box] AND ![contains_US]

Output

I hope this can be of some help.

[Saravanan13 · Accepted solution]

Thank you so much Yoshiro, That worked. I have one question what logic did you use for "Proceed formula"

[Yoshiro_Fujimori · Accepted solution]

@Saravanan13 ,

You mean the logic of this expression?

Proceed = 

[is_2023] AND [match_First_Name] AND [match_Middle_Name] AND [match_Last_Name] 

AND ![has_PO_Box] AND ![contains_US]

As all columns are in bool type,

when they are connected with "AND" operator, the result is TRUE only when all the values are TRUE.

In this case, you can interpret it as

The year is 2023 

First Name matches

Middle Name matches

Last Name matches

does NOT have PO Box  // because the value is negated with "!" at the head

does NOT contain US   // because the value is negated with "!" at the head

I like using "is_" "has_" in bool type columns as described in the book "The Art of Readable Code" (P27 Naming Booleans).