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

Applying value based on Data group value/sum

michalk36
7 - Meteor

Hello, 

 

I'm working on riddle and it's slowly bringing me to my knees.

I have the following dataset:

 

AmountGroupGroup items countTypeComment
-14689A2NLong position
0A2NLong position
-50B3NLong position
-7B3NLong position
-6B3NLong position
-59,384.00C3NAmend the position
       686.00C3NAmend the position
    3,082.00C3NAmend the position
-3768C YAmend the position
-3000D2NAmend the position
200D2NAmend the position
-200D YAmend the position

 

What my workflow should do in this case is:

  • If [Amount] records in [Group] are either 0 or negative - the [Comment] for all records should be populated with "Long position" and change [Type] to 'N' (example: values in group A, B).
  • If [Amount] records in [Group] are both positive and negative:
    • Workflow needs to sum negative values, and sum positive values, and create the rows for each summed values in [Amount] column (if there is 1 of value simply leave it as it is) - example: values in group C, D
      • If sum of positive values (in numbers) is less than sum of negative values (in numbers) - change the sum [Type] to 'Y' and [Comment] for all records to [Amend the position]
      • If sum of negative values (in numbers) is greater than sum of positive values (in numbers) - change the sum [Type] to 'Y' and [Comment] for all records to [Amend the position]

I hope that I described my "difficulty" and someone may propose a solution.

Thank you in advance.

3 REPLIES 3
Jon-B
7 - Meteor

Hi @michalk36 

 

Please check this, it is not a full solution but I ended up doing most of it and I think it's in line with what you're after. I'm concerned there are a few cases which you might not be caught in your logic and there appears to be no difference between your last two bullets, so the treatment ends up being the same for these cases. Again please check this is what you're after.

 

I have added numerous ERROR outputs where I think the logic is missing, so should you have any more test data to flow through, you may see these crop up.

 

Good luck with your further development.

 

michalk36
7 - Meteor

Hi @Jon-B 

 

thanks for sharing your idea! I'll check it out and get back with comments.

I talked with my colleague and he came up with idea to use cross tab with concatenation of each row in group and then feeding batch macro where calculation and decision will be executed.

Currently the solution is being executed with following macro:

michalk36_0-1615360753124.png

 

I'll try couple approaches.

Thank you for showing the way! 

michalk36
7 - Meteor

I managed to achieve the solution by taking the macro bit by bit, first I filtered all the values and then applied the formulas/calculations accordingly, once done I summed all the thing and in couple places transposed the columns.

@Jon-B thank you for sharing your idea - it gave me a nice overview how to solve the problem!

Labels