Multi-Row level Analysis

[alexis_d]

I have the  following input:

S.No	Budget Code	Description	Value
1	5001	XY	-474.42
2	5001	XY	245.05
3	5001	XY	474.42
4	5001	XY	-1222
5	5001	XY	1222
6	5001	XY	-1464.91
7	5001	XY	1.93
8	5001	XY	1464.91
9	5001	XY	-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.

[BenMoss]

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

[alexis_d]

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

[BenMoss]

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

[alexis_d]

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.

[alexis_d]

Sample Data

[alexis_d]

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.

[alexis_d]

@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.

[Thableaus · Accepted solution]

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.

- 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,

[saritajangra]

I had exactly the same requirement but solution seems to fail if both number in group are either positive or negative. For Ex.

5001	XY	1.93
5001	XY	1.93

Work good for positive - negative combination.

Though i tried few workarounds but it didn't work , anyone faced such issue ?