Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Different logic for related items

Highlighted
8 - Asteroid

Hi,

 

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.

 

How do I make this happen?

 

Thank you.

Highlighted
Alteryx Certified Partner

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.

 
 

Capture.PNG

Highlighted
8 - Asteroid

Hi @AbhilashR,

 

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

A1           1         5/21/20           100                                        amend                      amend

A1           2         5/21/20             0                                          book                         amend

A1           1         5/21/20           100                                        amend                      amend

 

Is this possible?

 

Thank you.

Highlighted
17 - Castor
17 - Castor

Hi @crazybeauti_ful 

 

We need a bit more detail.  In your example the current resolution is related to the code, i.e. 1=amend, 2=book.  Is this correct? 

 

In these cases, how do you determine the Should be Resolution? 

a. Is it the most frequent one?

b. Is there an implicit hierarchy in the code?  1 takes precedence over 2, 2 over 3, etc or some other system.  Please provide details

c. Is it always amend?

d. Is it some other method?  If so can you provide details

 

Dan   

Highlighted
8 - Asteroid

Hi @danilang,

 

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.

 

Can I just use a formula to resolve this?

 

Thank you,

crazybeauti_ful

Highlighted
17 - Castor
17 - Castor

Hi @crazybeauti_ful 

 

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

 

Dan

 

 

Labels