Group by ID and market

keepcalm

Hi Alteryx community,

I have this dilemma where I am trying to create a new field where if ID number and market the same,  I need to get an amount that is within ten percent of the previous row and to include the "No" and "Yes" in row 5 and 6 in the output given they belong to the same ID & market and fall within 10 percent range.

Given I have multiple records, taking only these 6 records as example I would need to create a new field to help in my output. 

So the final output is to take amount that is within 10 percent range based on previous row regardless of "Yes" or "No". Hope I'm not complicating things but appreciate the community help. Thank you

ID number	Market	Amount Transaction	New field
555	xyz	100
555	xyz	110	Yes
555	xyz	121	Yes
555	xyz	130	No
555	xyz	900	No
555	xyz	990	Yes

Sebastiaandb

Hi @keepcalm 

I'm not 100% sure if this is what you're looking for, but it's my best guess :-).

So the rules are:

- If ID number and Market of row 1 and row -1 are the same, compare;

- When comparing, check if the value of amount transaction on row 1 minus row -1 is equal to 10% of the transaction amount of row -1. 

What wasnt clear to me was if the amount had to be exactly 10%,as in your example, comparing 130 to 900 would result in over 10% but you stated is as a no, that's why i kept it at exactly 10%. Let me know if it helps!

Greetings,

Seb

Groupidandmarket.yxmd