6 Replies Latest reply on Aug 20, 2018 8:29 AM by Okechukwu Ossai

    Merge Calculated Fields?

    Jasim Ansari

      Hi,

      I have created 4 Calculated Fields with same column names containing different values for respective Fields. I would like to know if there is a way to merge all the 4 calculated field into one.
      For example, here you can see one of the calculated fields name Flags1 with values Null, 1, 2, 3,4,5,6. Similarly, I have other fields namely Flags2, Flags3, Flags4.

       

      Is there a way to merge all the Flags attribute into one and show it as a cumulative graph.
      Thanks
      Jasim

       

       

       

       

       

       

       

      Screen Shot 2018-08-20 at 14.50.46.png

        • 1. Re: Merge Calculated Fields?
          Okechukwu Ossai

          Not sure I understand your requirement fully. However, if the different flag fields are all of the same data type and are comparable. You could try;

           

          Flags1+Flags2+Flags3+Flags4

          • 2. Re: Merge Calculated Fields?
            Jasim Ansari

            thanks for the reply.

             

            I am looking for something like this:
            I have created 4 calculated field (flags1, flags2, flags3, flags4) and got its respective count. For example:

             

            Flags1:
            Null     1000
            1     20

            2     10

            3     5

            4     10

             

            Flags2:

            Null     500
            1     10

            2     20

            3     10

            4     50

             

             

            Flags3:

            Null     1000
            1     10

            2     20

            3     30

            4     10

             

            Flags4:

            Null     1000
            1     20

            2     20

            3     10

            4     10

             

            I am looking for something like this:


            Cummulative
            Null     3500

            1     60

            2     70

            3     55

            4     80

             

             

             

            As you said I tried  Flags1+Flags2+Flags3+Flags4, but I am only getting the null values.

            • 3. Re: Merge Calculated Fields?
              Okechukwu Ossai

              Ok, may need to understand the data structure a bit. Are these flags all on the same row level?

               

              Can you drag Flags1, Flags2, Flags3 and Flags4 to the Rows shelf and show me how they appear?

              • 4. Re: Merge Calculated Fields?
                Jasim Ansari

                They are not at the same row level

                Screen Shot 2018-08-20 at 16.44.05.png

                • 5. Re: Merge Calculated Fields?
                  chris monger

                  Hi Jasim,

                   

                  zn(flag 1) + zn(flag 2) etc. should do it for you, this will return a 0 instead of a Null where there is one, and not Null the rest of the calculation.

                  • 6. Re: Merge Calculated Fields?
                    Okechukwu Ossai

                    Hi Jasim,

                     

                    This is not straightforward because the fields are not at the same row level, so you can't perform a direct addition of the fields. You can use LOD to force the numbers to match at same row level and then sum them.

                    I'm assuming the data type of Flags1, Flags2 etc is Number(whole). If they are strings then you will have to modify the formula below. The formula is for when Flags1 is 1, 2, 3, 5, 6 and null

                     

                     

                    Create calculated field [Combined Flag]

                    IF [Flags1] = 1 THEN
                    {FIXED: MAX({FIXED [Flags1]: COUNT(IF [Flags1] = 1 THEN [Flags1] END)})} +
                    {FIXED: MAX({FIXED [Flags2]: COUNT(IF [Flags2] = 1 THEN [Flags2] END)})} +
                    {FIXED: MAX({FIXED [Flags3]: COUNT(IF [Flags3] = 1 THEN [Flags3] END)})} +
                    {FIXED: MAX({FIXED [Flags4]: COUNT(IF [Flags4] = 1 THEN [Flags4] END)})}

                     

                    ELSEIF [Flags1] = 2 THEN
                    {FIXED: MAX({FIXED [Flags1]: COUNT(IF [Flags1] = 2 THEN [Flags1] END)})} +
                    {FIXED: MAX({FIXED [Flags2]: COUNT(IF [Flags2] = 2 THEN [Flags2] END)})} +
                    {FIXED: MAX({FIXED [Flags3]: COUNT(IF [Flags3] = 2 THEN [Flags3] END)})} +
                    {FIXED: MAX({FIXED [Flags4]: COUNT(IF [Flags4] = 2 THEN [Flags4] END)})}

                     

                    ELSEIF [Flags1] = 3 THEN
                    {FIXED: MAX({FIXED [Flags1]: COUNT(IF [Flags1] = 3 THEN [Flags1] END)})} +
                    {FIXED: MAX({FIXED [Flags2]: COUNT(IF [Flags2] = 3 THEN [Flags2] END)})} +
                    {FIXED: MAX({FIXED [Flags3]: COUNT(IF [Flags3] = 3 THEN [Flags3] END)})} +
                    {FIXED: MAX({FIXED [Flags4]: COUNT(IF [Flags4] = 3 THEN [Flags4] END)})}

                     

                    ELSEIF [Flags1] = 5 THEN
                    {FIXED: MAX({FIXED [Flags1]: COUNT(IF [Flags1] = 5 THEN [Flags1] END)})} +
                    {FIXED: MAX({FIXED [Flags2]: COUNT(IF [Flags2] = 5 THEN [Flags2] END)})} +
                    {FIXED: MAX({FIXED [Flags3]: COUNT(IF [Flags3] = 5 THEN [Flags3] END)})} +
                    {FIXED: MAX({FIXED [Flags4]: COUNT(IF [Flags4] = 5 THEN [Flags4] END)})}

                     

                    ELSEIF [Flags1] = 6 THEN
                    {FIXED: MAX({FIXED [Flags1]: COUNT(IF [Flags1] = 6 THEN [Flags1] END)})} +
                    {FIXED: MAX({FIXED [Flags2]: COUNT(IF [Flags2] = 6 THEN [Flags2] END)})} +
                    {FIXED: MAX({FIXED [Flags3]: COUNT(IF [Flags3] = 6 THEN [Flags3] END)})} +
                    {FIXED: MAX({FIXED [Flags4]: COUNT(IF [Flags4] = 6 THEN [Flags4] END)})}

                     

                    ELSE
                    {FIXED: MAX({FIXED [Flags1]: COUNT(IF ISNULL([Flags1]) THEN [Number of Records] END)})}+
                    {FIXED: MAX({FIXED [Flags1]: COUNT(IF ISNULL([Flags2]) THEN [Number of Records] END)})}+
                    {FIXED: MAX({FIXED [Flags1]: COUNT(IF ISNULL([Flags3]) THEN [Number of Records] END)})}+
                    {FIXED: MAX({FIXED [Flags1]: COUNT(IF ISNULL([Flags4]) THEN [Number of Records] END)})}

                     

                    END

                     

                    Hope this helps.

                    Ossai

                    1 of 1 people found this helpful