Hi all,
Strange request for you all!
I'm looking to create a vertical list of all the differences found in a horizontal dataset. Ideally we want to make it easier for the end user to identify the differences found in the dataset in a cleaner condensed tab.
I've attached the ideal output below.
Many thanks in advance!
Ben
Solved! Go to Solution.
The logic applied here requires some tweaking to what is considered Old and New Data, and I will use the Date from Filename to be my indicator:
From there, it is a matter of sorting out the date that comes out of it - and since there's no Year component, we'll make do with 1400 as the date and month matter more.
From there, a Multi-Row Tool does the trick with our trusty IF statement to categorize Old and New Data.
Then, we use two Cross-Tab Tools to get the fields we want, and then just use a simple Formula Tool to get our Field of interest, and a simple Union to get it all together.
To make it dynamic, try using a chain app to get the fields of interest at will for users. This should give you some ideas!
-Cal
Hey @caltang
This looks great, thank you very much! I do note that the output is slightly different to the 'Required Output' in my attached file. Would you be able to slightly tweak your output if possible? Please also note that further mismatches may also occur on any field and not just 'significance' or 'control risk'. Would there be a way that we could ensure that any further breaks or mismatches are captured in your functionality. For example, if a break difference occurs on 'Control area' or any other applicable field?
Once again, many thanks for your help so far!
Hi @thompo511 - I'm going to split my replies into the following sections:
Please do not take my message in the wrong tone or way. I am genuinely curious as to why you wish for the output to be in that way. Because based on my workflow to you, it captures the values as is. This means that unless the data itself contains that difference, it won't be captured - granted that my workflow is static for now. Perhaps you can provide some random data that has that configuration and I'll see what I can do.
You can use Analytic Apps to get you the Fields of interest with a Drop-Down tool attached to the Formula tool (based on current configurations). This will allow you to choose the Fields of interest that you wish to replace in the Formula tool.
If you're going for something dynamic, then you'll need to use this: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Using-dynamic-select-in-an-ana...
Refer to DavidSkaife's solution.
If you want to go further - you can try using a Chain App. Like this: https://community.alteryx.com/t5/Interactive-Lessons/Chaining-Analytic-Apps/ta-p/243120
(Added Sort tool after the first post)
Hi @thompo511 ,
I tried to replicate your output image. I hope this works.
Workflow
Output
If you want to change the column name to "Old data" and "New data", you can use Select tool.
(Personally I prefer the current column names as it represents the original FileName.)
Hi @thompo511
There are many ways to implement data processing workflows, their complexity varies on how we parse, shuffle and special rules introduced by the expected results.
Expected results:
Solution Workflow:
Expression:
IF [Row-1:Record_Date]<[Record_Date]
THEN '2 New data'
ELSE '1 Old data'
ENDIF
The main reason for the 1 and 2 prefixes is the way the CrossTab tool sort the column.
Its fields names requiring adjustments, the Old data and New Data fields appeared in the expected sequence, as the tool sort the column names alphabetical, also notice the tool replaced spaces with underscores; another detail is with the Name column, which seems un-sorted.
I hope this helps,
Arnaldo
Hi @thompo511,
As you can see, @Yoshiro_Fujimori and @ArnaldoSandoval's solutions are similar to what I have provided to you earlier. Arnaldo's explanation is succinct, and you can see that the requirement for capturing a different "field" name in the row is not possible with the current data set.
Perhaps you can show us how you do it on Excel? What is the logic used to capture that field?
Hi @caltang
Let's wait for @thompo511 to clarify his expected results, as we found unclear rules to process the data.
cheers,
Arnaldo
Hi all,
Thanks for all your messages, sorry for the slow reply. @ArnaldoSandoval @caltang sorry I had the incorrect formula in one of my cells, hence the field name was incorrect! New data is now attached.
I think the main issue will be how the canvas picks up other fields that flag potential mismatches in the future. For example, if the mismatch falls outside Significance or Control Risk.
Please let me know if this makes sense, or if it will not be achievable!
Many thanks,
Ben
Hi @thompo511 et all,
I ran the workflow with the new files, the results are shown below
You shared a very minimalist sample with us, having only 4 records featuring changes on two fields, when you wrote:
@thompo511 wrote:...
I think the main issue will be how the canvas picks up other fields that flag potential mismatches in the future. For example, if the mismatch falls outside Significance or Control Risk.
...
Would you be able to prepare a new sample file featuring mismatches outside Significance and Control Risk? these are some of the questions I have myself:
Questions:
Cheers,
Arnaldo