This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am new to Alteryx and am currently working on a report comparison workflow.I am not exactly sure if this is doable with Alteryx.
I have very little idea about Macros in Alteryx and does not have a coding background
So the basic idea is,there will be two reports with identical headers,but with different values for them.
This reports can have duplicate records in each,unique records in each (which are different records which are unique to each),and different in number of records.
I am trying to map all the unique records,so that i will only be left with the union of un joined records in each of the report.
My idea is to separate the unique (Joining unique and writing the un joined records to output) and duplicates (using unique tool) and pass the duplicates in each of the file in a macro.And inside the macro,do the same (separating unique and duplicate and keep writing(appending) the un joined unique to the initial output till there are no records in either of the report).
Thanks Charlie S.But the files i am using will have multiple duplicates in both.A small bit of design is added in reply.
When i get duplicates from the files,i would need to use that as the input and again do a unique on individual file (records left after removing the unique the first time) and keep doing this till there are no more records available in any of the file (any one file is left with records and the other is not,i will take a union of Left and right output of Join and mark them as un mapped.
I think the most reliable way to make iterative macro is to design it so that the schema is consistent beginning to end. What this means in scenarios like this:
Union all records from both files and add two columns Source (file1, file2, etc) and match. Match should start out as 0/null, but each iteration, in updating the match column which whatever value is relevant. At the end of the iterative macro, I append the count of unmatched/unmapped records to all records, and filter if the number is greater than 0 (loop) or not (final output). Then add a select tool before each output to remove the count field so that each output is given a field schema that matches the original input. All records are looped every time with a consistent field schema. This will keep the macro looping if there's any work to be done, or output the results if not.
If i am to union both files (with Source and match column to differentiate them),will it not match the duplicates as well (Eg : say there are 4 duplicates in File 1 and 3 in File 2,will it not update all the 4 records as matched ?).I want the result to say the 3 records in File one have matching records in File 2 and the 4th record in file 1 marked as "Matching records not found"
I am still having difficulty in setting up the batch macro,so couldn't run it to verify the results.
Ok, now that I've seen your example data, I've got a different approach that doesn't require a macro.
The attached solution uses the Tile tool to not only identify unique values, but how many of each unique value occurs in each file. This way unique values are joined on both fields so the total records remain consistent.
Sorry for the delay in response.I tried with another approach.
First i will groupby the data with all the column in groupby and take a count (for both the files)
then join then on all the columns.This way i will get the un joined ones in Left and right output of the join and the ones that are joined i will use the formula to get the Left count -Right count.
If the the count is != 0,I populate those records as output along with the unjoined (Left and right output from join).for the records I will also set from which source file the additional records are present using the formula tool