Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Data Preparation

Ragini1
8 - Asteroid

Hi All,

 

I am trying to build logic for the below scenario :

Input>>

AccountTypeValue
1000A20
2000B-30
3000A10
4000A10
5000B-10

 

Output>>

AccountTypeValueComments
1000A20 
2000B-30Offset
3000A10 
4000A10 
5000B-10Offset

 

Can someone please help with this?

 

Regards

6 REPLIES 6
Luke_C
17 - Castor

Hi @Ragini1 

 

You can use a formula tool with an if statement for this

 

Luke_C_0-1618864533943.png

 

 

Ragini1
8 - Asteroid

My question was, if Type =B and if sum of any combination of Type A +type B = 0 then in comments column for Type B , I should have comment as offset entry

Luke_C
17 - Castor

Hi @Ragini1 

 

Thanks for laying out the logic. Try this:

 

Luke_C_0-1618865595622.png

 

Ragini1
8 - Asteroid

Thank you for the help.

 

Input>>

AccountTypeValue
1000A20
2000B-30
3000A10
4000A10
6000B30
5000B-10

 

 

Output>>

AccountTypeValueComment
1000A20 
2000B-30Offset
3000A10 
4000A10 
6000B30 
5000B-10Offset

 

The logic doesn't work if we have the above input. Can you please provide your feedback?

Luke_C
17 - Castor

I think you'll need to provide some more requirements. How are we grouping the totals of A & B? In this case doing total of A minus total of B does not = 0, so nothing is flagged. Its not clear to me how we would systematically get to the answer you're looking for. If you're doing it in excel now can you share the formulas you're using?

mattnason1
9 - Comet

Here's how I would handle if I understand what you're trying to do.

 

Just a quick rundown of my process.

1) Sort records by dollar low to high.

2) Create a running total of the values.

3) Join on the dollar if they zero out.

4) Take the highest dollar value from Group A that gets offset.

5) Add the comment offset to every record from Group B that adds up to the Group A record.

 

I might be misunderstanding what you're trying.  But it might work for your purposes.  It did work for both examples.

Labels