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 have 2 look-up files joined to my main file via acct+code+date. If my code is "1" then Alteryx should get the details in look up file no.1, if my code is "2", then Alteryx should get the details in look up file no.2. I found out in my testing that there are some accounts with related line items with multiple codes therefore Alteryx is getting data from both look up files even though they should reflect the same details.
Account Code Date Amount (from look up files)
A1 1 5/21/20 100
A1 2 5/21/20 0
A1 1 5/21/20 100
In this example, Alteryx should be getting 100 from file no. 1. The end goal of this workflow is a resolution for each line item. In this scenario, line no.2 if getting different resolution from the rest of the related items because of the amount difference. All 3 of them should just reflect one and the same resolution.
Hi @crazybeauti_ful, are running into the challenge of having unnecessary duplicates due to how joins and processing your files, and are looking for a way to de-duplicate? It was a bit hard for me to understand the ask here and I apologize if I am not addressing the heart of your question.
Apologies for the confusion. Actually, I was thinking of duplicating the resolution per acct+code+date as the overall solution. The samples are correct, nothing should be deleted. I added the resolution columns for more clarity.
Acct Code Date Amount (from look up files) Current Resolution Should be Resolution
The resolution is dependent on another set of calculations - straightforward logic for straightforward items i.e. 1=amend, 2=book. My issue is on special cases when "2" should be "amend" as well or "1" should be "book" depending on where the details in the source files are. To your question, I think this is more of b. Is there an implicit hierarchy in the code? 1 takes precedence over 2, 2 over 3, etc or some other system.
If your rule is something like "take the min code" then use a summarize tool to get min for each acct and date, join this back to your data on acct and date and use this code to determine your Should be.
If your rule is more complex, use the same process but replace the summarize tool, with a sequence of tools to return the correct code to apply to each Acct and date. If you can build an algorithm for rules and provide some sample data we can help you translate that into a set sequence of tools