6 Replies Latest reply: May 15, 2014 9:53 AM by Nigel Winton RSS

    Calculated Fields and If function

    Wendy _

      Hi, 

      I have report with Product Codes. Based on these product codes, I need to categorized them into separate dept and types. There are multiple product codes for one dept or one type. My objective is to have one product code column, one dept column and one type column.

       

      I tried using the if function and tried embedding

      the function but it return with operand types or invalid expression. ie: if(value, true, if (value, true, false) false)

       

      However, I did manage to use the if function to separate each product codes into separate depts with each depts in one column. I have 3 columns for dept now and no idea how to merge these 3 columns into 1 and get to my objective.

       

      Can someone please help?

      Thanks,

        • Calculated Fields and If function
          Nigel Winton

          Wendy

          Not quite sure what you want, but you can merge columns by using a calculated field with the + sign, as Field1+Field2. You might end up with spaces where you don't have data in a particular column, but these can be removed with the Trim commands. If you could post an example of your data it would help, and I might be able to give a better solution.

           

          Regards

           

          Nigel

          • Calculated Fields and If function
            Peter @DeScenza

            Originally posted by Wendy:

            Hi, 

            I have report with Product Codes. Based on these product codes, I need to categorized them into separate dept and types. There are multiple product codes for one dept or one type. My objective is to have one product code column, one dept column and one type column.

             

            I tried using the if function and tried embedding

            the function but it return with operand types or invalid expression. ie: if(value, true, if (value, true, false) false)

             

            However, I did manage to use the if function to separate each product codes into separate depts with each depts in one column. I have 3 columns for dept now and no idea how to merge these 3 columns into 1 and get to my objective.

             

            Can someone please help?

            Thanks, /b[/quote]

            • Calculated Fields and If function
              Peter @DeScenza

              I am trying to post a response but I am having problems.  If you email me at pc@descenza.com[/email] I will send you the solution I use.

              • Calculated Fields and If function
                Wendy _

                Nigel,

                Your solution worked. From the 3 separate Dept column, I was able to use the "+" function. 

                But I wonder if there is an easier way to get this is one step. Here's a sample of my data.

                PRODUCT CODE

                107

                127

                107

                109

                117

                137

                117

                 

                Using the calculated field, I wanted monarch to create a Dept column such as example below:-

                PRODUCT CODE      DEPT

                107               ADMIN

                109               ADMIN

                117               MARKETING

                127               PRODUCTION

                137               ADMIN

                 

                Thanks,

                • Calculated Fields and If function
                  Bruce _

                  Wendy

                   

                  Have you tried a lookup function? Looking at the data that seems like the quickest way to do what you want.

                   

                  Create a new calulated field, lookup type, and the rest should be self explaintory, but if not post back.

                   

                    smile.gif[/img]

                  • Calculated Fields and If function
                    Nigel Winton

                    Wendy

                    Bruces Lookup idea will allow you to put the names in and then a Summary would let you summarise the data as you need. Keep experimanting, the answer is out there.

                     

                    Nigel