5 Replies Latest reply on Jul 13, 2018 10:25 AM by fiona.lawrence

# Nulls from a calculated join

I've been trying to use a calculation (see below) to create a field with a currency code in it, made up of The year of an event start date, followed by the month (with a leading zero if not double digit) followed by the currency held at the start of a text field that also contains value.

I then use this to look up a value in a separate source that has currency exchange rates for that month (monthly rates)

For some reason, I'm getting nulls from the spot monthly rates

if [Total Contracted Value] = NULL THEN ""

ELSE

((STR(YEAR([Event Start Date]))+

IF MONTH([Event Start Date])>9

THEN STR(MONTH([Event Start Date]))+LEFT(([Total Contracted Value]),3)

ELSE STR(0)+ STR(MONTH([Event Start Date]))+LEFT(([Total Contracted Value]),3)

END

Can anyone spot the problem in the attached?

• ###### 1. Re: Nulls from a calculated join

Hi Fiona;

Do you have a workbook you could share? Without seeing it, here are a few suggestions:

You've got an equation with a lot of moving parts there; I always find it helpful to break things down into smaller, "digestible" pieces before stringing them together.

I'd begin by listing your Event Start Date and create a calc for each segment of your large calc:

STR(MONTH([Event Start Date])

STR(0)+ STR(MONTH([Event Start Date])

LEFT(([Total Contracted Value]),3)

Total Contracted Value

See what each of these calcs return. You may find LEFT(([Total Contracted Value]),3) is the problem child when your values are <100 (but I'm not sure)

You should also be able to shorten your equation using this:

RIGHT("0"+STR(MONTH([Event Start Date])),2)+LEFT(([Total Contracted Value]),3)

if [Total Contracted Value] = NULL THEN ""

ELSE

((STR(YEAR([Event Start Date]))+

RIGHT("0"+STR(MONTH([Event Start Date])),2)+LEFT(([Total Contracted Value]),3)

END

As someone who regularly uses generated ID's, I would also encourage you to put a delimiter between the different parts so you can more easily identify what your computations are doing; it's just my preference, but I like 2018-01-926 over 201801926.

Good luck --Michael

• ###### 2. Re: Nulls from a calculated join

Thanks sorry thought I added the workbook...probably helps

• ###### 3. Re: Nulls from a calculated join

To check for nullability, the correct function to use is ISNULL().

Change if [Total Contracted Value] = NULL to if  ISNULL([Total Contracted Value])

• ###### 4. Re: Nulls from a calculated join

I Made Slight change in calculation :

if ISNULL([Total Contracted Value]) THEN " "

ELSEIF MONTH([Event Start Date])>9

THEN STR((YEAR([Event Start Date])))+STR(MONTH([Event Start Date]))+LEFT(([Total Contracted Value]),3)

ELSE STR((YEAR([Event Start Date])))+STR(0)+ STR(MONTH([Event Start Date]))+LEFT(([Total Contracted Value]),3)

END

Output as below :

Hope it helps!

• ###### 5. Re: Nulls from a calculated join

Hi

Yes, the calculation is exactly what I'd expect to see and I see the same results if I use my original calculation (see my workbook) however, I don't understand why I get no matches to the Monthly rates sheet when using this calculation for the join?