7 Replies Latest reply on Dec 15, 2016 5:29 PM by sherry.shaik

# Calculation for categorizing with conditions

Hi all,

can someone help in creating calculated field for categorizing comparisons between two systems?

 1 Works If field1 & field2 matches 2 Doesn't works If Field1 & Field2 doesn't matches(No Nulls considered) 3 BOTH NULLS If both Field1 & Field2 are NULLS 4 Field1 NULL If Field1 is Null & Field2 is NOT NULL 5 Field2 NULL If Field2 is NULL & Field1 is NOT NULL

Thanks

• ###### 1. Re: Calculation for categorizing with conditions

Sherry,

Please attach packaged workbook.

Thanks,

Shin

• ###### 2. Re: Calculation for categorizing with conditions

I can attach the packaged workbook but it's really very vague and difficult to understand. Do you still want to see the workbook?

• ###### 3. Re: Calculation for categorizing with conditions

Sherry,

If the question is vague and difficult to understand, it's difficult to answer, as you can easily understand.

If your question becomes clear, it's relatively easy to create mock-up data/packaged workbook.

And we need packaged workbook to make our investigation easier.

Thanks,

Shin

• ###### 4. Re: Calculation for categorizing with conditions

Look in Comparison Data source for 'Material description' calculated field. It has just 3 categories but i need to change to 5 like mentioned above.

I really appreciate your help.

• ###### 5. Re: Calculation for categorizing with conditions

Found it:

This worked with me,

IF FIELD1 = FIELD2 AND NOT ISNULL(FIELD1)

AND NOT ISNULL(FIELD2)

THEN "Matches"

ELSEIF FIELD1 != FIELD2 AND NOT ISNULL(FIELD1)

AND NOT ISNULL(FIELD2)

THEN "Mismatches"

ELSEIF ISNULL(FIELD1)

AND ISNULL(FIELD2)

THEN "Both Nulls"

ELSEIF ISNULL(FIELD1)

AND NOT ISNULL(FIELD2)

THEN "MDM is NULL"

ELSEIF NOT ISNULL(FIELD1)

AND ISNULL(FIELD2)

THEN "BTB is NULL"

ELSE NULL

END

NOTE: Functions used are

IF

ELSEIF

NULL()

ISNULL()

NOT

AND

THEN

END()

• ###### 6. Re: Calculation for categorizing with conditions

Great job Sherry.

Actually that (Found the answer by your self) is very beneficial to you to learn the tricks thoroughly

Thanks,

Shin.

• ###### 7. Re: Calculation for categorizing with conditions

Thank You Shinichiro.

I really appreciate your patience and kindness. I would definitely try myself and then post in here if i couldn't find answer.

Regards,

Sherry.