community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Multi-Row level Analysis

Highlighted
Meteor

I have the  following input:

S.NoBudget CodeDescriptionValue
15001XY-474.42
25001XY245.05
35001XY474.42
45001XY-1222
55001XY1222
65001XY-1464.91
75001XY1.93
85001XY1464.91
95001XY-1462.98

 

Now, I want to add a column 'Comments'. The rule for comment is to identify the rows in which the sum of value is net to nil. Suppose the value in row 1 (-474.42) is appearing with the opposite sign in row 3 (474.42) and therefore, the comment against these two rows will be 'Net to Nil'. For cases where the values don't net to nil, the comment column will be ( 'To be analysed').

How do I design the workflow in Alteryx for the same? Any help would be greatly appreciated.

 

Alteryx Certified Partner
Alteryx Certified Partner
Here's how I would go about this process...

1. Create a new field which is the absolute value so ABS([Value])

2. Record ID your dataset (although. you have this already with the S.No field)

3. Join the dataset to itself, matching on your newly created absolute value field.

4. From your J stream, filter out records where the data has matched to itself (using the record ID on each side)

5. Filter to only keep records where the real value is different on each side (I.e. the absolute is the same but the value is different, I.e. one positive and one negative).

6. Your true stream from the above filter will then contain your 'net to nil' records.

Ben
Meteor

Hey Ben,

 

Thanks for your response.

I have understood your approach. However, I am not sure how to design the workflow for the same. C

Could you please provide a sample workflow?

 

Thanks again for your help

 

 

Alteryx Certified Partner
Alteryx Certified Partner

Hi @alexis_d, I would prefer it if you took some time to try and build the approach that I have suggested and then you pose questions where you struggle to implement the logic, this problem solving process will be a much better learning experience than me just giving the answer.

 

Ben

Meteor

Dear Ben,

 

I appreciate your approach and truly believe it will provide a better learning experience.

As per my understanding, I tried designing the workflow. However, I am not getting the correct output.

Please help me understand where did I go wrong.

 

The steps are:

 

1. Upload the data using input tool (No of rows:- 53)

2. Calculate the abs_value using formula tool

3. Add the record id using record id tool

4. Use the Join Tool and used the input in both left and right connections and join on the basis of abs_value. Post that, the output contains 115 records

 

Now, how do I go ahead? I have attached my workflow for your review.

Meteor

Sample Data

Meteor

If you could just elaborate point 4 because I don't understand. And also, the number of records from step 3 have gone from 53 to 115.

Meteor

@BenMoss  I have revised the workflow a bit.

 

However, I am still struggling a bit to get the correct output.

Request your help for the same.

Alteryx Certified Partner
Alteryx Certified Partner

Hi @alexis_d 

 

I'd go for a different approach then @BenMoss's.

 

That doesn't mean Ben is wrong. There are many ways to do what you're trying to do.

 

Multi-Row.png

- Change Value to Double Type

- Use ABS Value formula

- Add a Record ID

- Sort ABS Value Ascending - (here you could also sort by Code first or Description if you need to group by any of those).

- Use Tile Tool to assign a sequence for each Unique ABS Value that is found

- Use Multi-Row Formula Tool

IF the Tile ID is an odd number (use Mod function to identify), but there's a match in the next row (that means, that ABS value has a corresponding value), then "Net is Nil" 

OR IF the Tile ID is an even number (if it's even, that means it has a corresponding unique ABS value) - "Net is Nil"

ELSE "To be Analyzed"

- Sort by Record ID again - that means, put everything back to order

- Deselect everything else from the Dataset.

 

WF appended. Change the input to your local path.

 

Cheers,

Labels