Conditional Field Formula

Mario36

Here's what I want to achieve, If a column contains the value '1' then the next columns should be null / blank. Grateful if someone could help me achieve this.

BEFORE
FieldName	Group_1	Group_2	Group_3	Group_4	Group_5	Group_6	Group_7
Group_1	1	1.1	1.2	1.3	1.4	1.5	1.6
Group_2	1.1	1	1.1	1.2	1.3	1.4	1.5
Group_3	1.2	1.2	1	1.1	1.2	1.3	1.4
Group_4	1.3	1.3	1.2	1	1.1	1.2	1.3
Group_5	1.4	1.4	1.3	1.2	1	1.1	1.2
Group_6	1.5	1.5	1.4	1.3	1.2	1	1.1
Group_7	1.6	1.6	1.5	1.4	1.3	1.2	1
AFTER
FieldName	Group_1	Group_2	Group_3	Group_4	Group_5	Group_6	Group_7
Group_1	1
Group_2	1.1	1
Group_3	1.2	1.2	1
Group_4	1.3	1.3	1.2	1
Group_5	1.4	1.4	1.3	1.2	1
Group_6	1.5	1.5	1.4	1.3	1.2	1
Group_7	1.6	1.6	1.5	1.4	1.3	1.2	1

NicoleJohnson

You should be able to get to this desired result with a little pivoting of your data + a Multi-Row formula. These are some more intermediate data prep concepts, but the general idea is to get your columns into rows so you can group by each FieldName field and check to see if the prior row is a 1 (or if it was set to blank because it saw a 1 in a previous field). Once you've identified what your new values should be, the Cross-Tab tool will pivot the data back into the right format. See attached for an example (including the formula that should get you the right info for checking if 1 was previously reached for each grouping).

Hope that helps! 

Cheers,

NJ

ConditionalFieldFormula_Solution.yxmd