Dynamically comparing the content of columns by pairs and flagging update needs per user

[Misulici]

Hey guys,

I've found myself in a rabbit hole trying to figure out something in Alteryx.

I am working on vamping up an existing workflow and have decided that it best to start from scratch. 😅

The catch is I would want to teach it to compare the values between paired columns and flag where it identifies a difference. Of course, if the value is null, then there is no need to mark the need for a change.

The columns that start with "Input_" represent the values that someone would request, whereas their pairs, that do not hold "Input_" at the beginning represent the currently existing values.

Am attaching as well an idea of an input.

Another catch is that the column names will tend to change, however they will keep the same pattern: Input_X and X.

Any ideas?

Am thinking of the Multi-Field Formula, but I admit that I've had limited interactions with it. Am also thinking of the option to create it into a macro and then assign it to the other flow that would bring the data.

[KimLamNg]

I'd suggest you use Transpose and Cross tab to do this. First transpose all the columns into two columns Name and Value, Name is the field name and Value is the value in the field. Don't forget to select ID fields as Key columns. So in the Name column, you will have Input_X and X and their corresponding value in Value colum. Next, create a lable column for Input vs Non-input via a simple formula like if contains([Name],"Input_") then "Input" elseif "Non-input" endif. Then standardized the "Name" column to only have the variable name (i.e. only X, Y, Z, etc.) Finally, use a Crosstab to transpose back but this time, keep column Name as a Group column, Column header will be the label column and value will be value. So the output should look sth like this:

ID     Variable name    Input value    Non-Input value    

aaa   X                         123                 124

bbb   Y                         456                 457  

[DataNath · Accepted solution]

Hey @Misulici, how does something like this look?

We pivot the columns before stripping 'Input_' to find which need comparing (using a Join). Then we can compare the 'Input_' with incoming/already existing value and if one is null and the other isn't, class it as fine. Equally if they match, otherwise flagging it as 'Different'. Now we have our flag columns we join this back to the starting dataset based on the Record ID (+ User ID in your case) to leave our final output.

[Misulici]

@DataNath thank you lots! It actually worked perfectly! 

Guys, @KimLamNg  & @DataNath, thank you both for sharing your ideas and insights! :)