on 12-14-2015 02:02 PM - edited on 07-27-2021 11:45 PM by APIUserOpsDM
Data Integrity refers to the accuracy and consistency of data stored in a database, data warehouse, data mart or other construct, and it is a fundamental component of any analytic workflow. In Alteryx, creating a macro to compare expected values to actual values in your data is quite simple and provides a quality control check before producing a visual report. Let me show you how to build this.
The two inputs represent the actual and expected values in your data. These data streams are passed through a Record ID tool to keep positional integrity and then passed on to the Transpose tool to create two columns. The first column contains the field names and the second column shows the values within each field. This data is then passed on to a join, matching on Record ID and the Name of the field, in order to compare each value. Lastly, if the data does not match from expected to actual, a custom message will appear in the results messages alerting the user where the mismatch happened within the dataset. The image below shows the error message produced if values differ across datasets.
Hello,
I'm trying to identify a way to build in a workdlow step to compare two data sets based on a percent variation or a number variation and notify me if the comparison shows that the variation is greater than a set threshold. For example: I have an open accounts receivable file from May that has 500,000 accounts listed and I have an open accounts receivable file from April that has 700,000 accounts listed. The variation between them being 200,000 accounts. I want to build a compare function that will automatically alert me if the variation between the two is greater than say 50,000 accounts. How would I go about doing that?
Thanks!
Hi @MandeB,
First you will need to create a count for each month which can be done in the Summarize tool. Then I would use the formula tool to create a new field that calculates the difference between the two months. Once you have this new field, you can then use an if/then statement to flag any counts greater that the 50,000 account threshold.
Hi Tony,
I have a requirement where in some business logic has been applied when moving data from source to target.
Source is denormalized i.e. flat structure and target is normalized i.e. Name vaue pairs.
Can I use this macro for such cases as well, main query is if there is a formula applied when moving data from source to target, can a reverse formula be applied when comparing fields.
Thanks
Rohit
Hi @TonyM,
I have two data sets with 15 columns and wanted to compare and every column of each row and find out any data mismatch. Please let me know if you have any example mapping any video link, please let me know that is much appreciated.
Tried to implement from above but cant find one of tool after join in above diagram. could also please let me know what is the tool name and how its configured.
Please let me know any videos on this requirement.
Thanks
Ramu
Pls tell me How to perform MINUS using Altreyx tool...
I am a ETL tester,this function is very much needed...Currently I am doing join and comparing every column whcih is little bit tideous job. I want all the source and target columns shold be compared in a one go like MINUS .....Plsss help me....
This is a great write-up! Thanks!
I would like to create a message when I have in-joined records. If I have more than 0 right or left in-joined I would like to create a flag for myself. What is the "message expression" I should use? Do I need a message tool after each join? Do I need separate tools for left joins vs. right joins?
My previous comment was asking about un-joined records not in-joined - the helpfulness of spell-checkers :(
Thank you very much, it helped a lot and I learned to use a new tool
Helped a lot thanks.