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

How to compare row values based on values from another field?

owenkosnen
8 - Asteroid

Hi Experts,

 

I am a new user in Alteryx and would like to seek advice from the community. This task should be simple but due to limited knowledge of Alteryx tools, I don't seem to be able to find solution for these..

 

Below is my question:

 

Question 1: I have a table as below screenshot and would like to fill in the value for Count, Expense, Avg_Expense of 'Diff', which is basically the difference in value of "T+1" minus "T". Some thread suggests Multi-Row Formula Tool but I'm not quite sure of how to use this tool and wonder if anyone would guide me on this..

 

Question 2: After filling in the values for 'Diff', I'd like to transpose the field into the second table below, which would be my end result.

I tried using transpose and crosstab but not able to get the result I want. Perhaps I am missing some other tweaks needed for this..

 

alteryx question.png

 

 

Any idea/suggestions is much appreciated! Thanks in advance!

 

 

 

 

9 REPLIES 9
wwatson
12 - Quasar

Step 1 requires a multi-row formula tool.

 

Step 2 requires a cross-tab tool.

 

You should be good then.

owenkosnen
8 - Asteroid

Hi @wwatson ! Thanks for your reply. Would you be kind enough to demonstrate how to use Multi-Row Formula tool specifically for this case?

 

 

grazitti_sapna
17 - Castor

Hi @owenkosnen,

 

Below is the solution to your problem:

 

Comparing row Values.png

 PFA workflow as well. I hope it helps. 

Sapna Gupta
szade1
8 - Asteroid

Hi @owenkosnen ,

 

This is the simplest I can come up with:

1) Use of multi row formula wherever "difference" is required

2) Transpose to get the uniform format

3) Crosstab to get the desired result.

szade1_1-1596016977174.png

 

Hope it helps!

 

Thanks,

S.

Ben_H
11 - Bolide

Hi @owenkosnen 

 

Here's another method that doesn't use multirow formulas.

 

1. I transpose the data first, then construct a header field.

2. Crosstab the data using the new header field.

3. Use a normal formula tool to calculate values.

4. Use a select tool to re-order the columns as required.

 

Ben_H_0-1596020949560.png

 

Regards,

 

Ben

 

owenkosnen
8 - Asteroid

Thanks a lot! this is very helpful.

owenkosnen
8 - Asteroid

Wow, now I know how to use multi-row formula a bit. Thanks for your help!

nareshns8
5 - Atom

.

nareshns8
5 - Atom

.

Labels