Multi-Row level Analysis

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.

Developer

Transformation

Dynamic Processing

Preparation

Accepted answers

Thableaus

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,

MLR_Solution.yxmd

All comments

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

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.

MLR_Solution.yxmd

Sample_Data.xlsx

Thableaus

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"