Row Wise comparison

[maverick_70]

Hello,

I am trying to compare values in multiple columns row wise. There are tow parts to the problem;

1. I need to assign a sequence to the table that repeats itself for each value in column 1 as (P,O,I,B,M). 

2. The values in column 2 are sequenced(P,O,I,B,M).  I want Alteryx to go down each row with respect to value in column 1 and column 2. If the value is null in column 3 then it should skip these rows and find the row(s) which is not empty and compare this level with the one right below it (if not null). Alteryx should take the value from column 3 from the first non null level and multiply it with the column 4 and column 5 values for the rows below it. 

The pattern should repeat for each value in column 1.

eg; 

For AAAA in column 1 if the 'B'  quantity is 20 or something else, and there is no 'P', 'O' or 'I'  value in column 3(null or empty) then I have to verify that 20 X the 'M' value in column 4 and 20 X the 'M'  value in column 5 is within 5% of the 'B' level values in column 5.

For CCCC it would do it twice since there are tow levels right below 'I'.

Column1	Column 2	Column 3	column 4	column 5	Result( Within 5%?)
AAAA	P
AAAA	O
AAAA	I
AAAA	B	20	6	2973
AAAA	M	1	0.2	77
BBBB	P
BBBB	O
BBBB	I
BBBB	B	20	5	3013
BBBB	M	1	0.2	76
CCCC	P
CCCC	O
CCCC	I	1000	3	154
CCCC	B	6000	16	972
CCCC	M	100	0.5	24

your hep would be much appreciated.

[RolandSchubert · Accepted solution]

Hi @maverick_70 ,

if I got it right, it should be possible to do that using a Multi-Row Formula tool. Basically, you want to compare values within two different rows (before comparing there are some calculations).

IF IsNull([Row-1:Column 3]) THEN  -> Value in previous row is NULL
Null()   -> Result Null

ELSE
IF ABS([Row-1:Column 3] * [Column 4] + [Row-1:Column 3] * [Column 5] - [Row-1:Column 5]) <= 0.05 THEN  -> result of multiplication within 5% deviation
"Y"
ELSE
"N"
ENDIF

ENDIF

To ensure, that the comparison is only done for rows with the same value in Column 1, you can group by that column. I've attached a sample workflow.

Does this help?

Best regards

Roland

[maverick_70]

Thank you Roland! The solution worked!

[Bansi08]

Hi Ronald -- I was looking for such function to compare row wise returns comparison. here in my case i am having below sample table and i want to perform comparison of returns row wise and on ACCT_NUM+SEGMENT+END DATE level if any record breach it by 5% then it should populate as Yes in next column.  

Can you please help me here. 

I tried attached but it seems not working

ACCT_NUM	SEGMENT	START_DATE	END_DATE	ROR
1234	Gross	12/31/2021	1/1/2022	0
1235	Gross	1/1/2022	1/2/2022	0
1236	Gross	1/2/2022	1/3/2022	-0.3907
1237	Gross	1/3/2022	1/4/2022	-0.9805
1238	Gross	1/4/2022	1/5/2022	-0.7017
1239	Gross	1/5/2022	1/6/2022	0.01986
1240	Gross	1/6/2022	1/7/2022	-0.1403
1241	Gross	1/7/2022	1/8/2022	0
1234	Net	12/31/2021	1/1/2022	6
1235	Net	1/1/2022	1/2/2022	0
1236	Net	1/2/2022	1/3/2022	-0.0917
1237	Net	1/3/2022	1/4/2022	-0.1895
1238	Net	1/4/2022	1/5/2022	-6.7877
1239	Net	1/5/2022	1/6/2022	0.05245
1240	Net	1/6/2022	1/7/2022	-0.2653
1241	Net	1/7/2022	1/8/2022	0