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.
Solved! Go to 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
Thank you Roland! The solution worked!
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 |