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

    Nulls from a calculated join

    fiona.lawrence

      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?

       

      Thanks for any advice offered.

        • 1. Re: Nulls from a calculated join
          Michael Hesser

          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)

           

          Your equation becomes:

          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
            fiona.lawrence

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

            • 3. Re: Nulls from a calculated join
              kunal.dongre.0

              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
                Padma Yalavarthi

                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
                  fiona.lawrence

                  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?