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

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

RowWiseComparison.yxmd