Replacing values

bdsibert

I have a dataset with column SF_PLAN_ENTITY_CD where the values are either 5, 6, 7, 8, 9, or 10. I also have columns INC and BOARD where the values are either Y or N. I am looking to scan the dataset and if the SF_PLAN_ENTITY_CD = 5 AND INC = Y AND BOARD = N, then replace the 5 with a 7. If the filters are not met, SF_PLAN_ENTITY_CD should remain the same.

So for example,

SF_PLAN_ENTITY_CD  | INC | BOARD

5                                        Y        N            --------> SF_PLAN_ENTITY_CD is replaced with 7

6                                        Y        N            --------> SF_PLAN_ENTITY_CD remains a 6

5                                        N        N            --------> SF_PLAN_ENTITY_CD remains a 5

This was the initial thought that I had, but I am only returning zeroes in my output file.

IF ([SF_PLAN_ENTITY_CD]=="5" AND [INC]=="Y" AND [BOARD]=="N") THEN [SF_PLAN_ENTITY_CD]=="7" ELSE [SF_PLAN_ENTITY_CD]=="5" ENDIF

Any thoughts would be greatly appreciated!

DataBlender

Hi @bdsibert, you can use the formula to update the SF_PLAN_ENTITY_CD field.

Try the below (which assumes that SF_PLAN_ENTITY_CD is a numeric field type, if not, surround the 5 in inverted commas):

IIF(
[SF_PLAN_ENTITY_CD]=5 AND
[INC] = 'Y' AND
[BOARD] = 'N',
7,
[SF_PLAN_ENTITY_CD])