6 Replies Latest reply on May 4, 2018 3:25 PM by David Núñez

    Classify repair order

    David Núñez

      Hello everyone.

       

      I work at a car service workshop. I'd like to classify my data depending on the type of maintenance the vehicle has been serviced with. My data is structured like this:

       

      Repair OrderActivity CodeActivity description Cost
      Price
      345MAASGMaintenance AXXX

      YYY

      345BGHClean brakesXXXYYY
      345DFCClean carXXXYYY
      345HJRIWQRepair xXXXYYY
      34501344Repair YXXXYYY
      347MABSGMaintenance BXXXYYY
      347035531Repair XXXXYYY
      3471847872MaterialsXXXYYY

       

      And so on for many other repair orders, I'd like a calculated field that would include four activity codes that help us identify maintenances by types. In a previous question someone suggested a flag CF:

       

      if CONTAINS([Activity Code],"MAASG") THEN "Maintenance A" ELSE "NO" END

       

      However in this case, I'd like to include all the possible maintenances; A, B, C, D. This would help me know which kind of maintenance the repair order has been serviced as well as all the other activities the order came for.

       

      Thank you in advance.

       

      Best,

      David

        • 1. Re: Classify repair order
          Shinichiro Murakami

          HI David

           

          It's difficult to understand your expected results.

          Could you explain it with some illustration or example numbers.

           

          Thanks,

          Shin

          • 2. Re: Classify repair order
            David Núñez

            Hi.

             

            My data would look a bit like this with this CF.

             

                 

            Repair Order

            Type of maintenance

            Activity Code

            Activity description

            Cost

            Price

            345Maintenance AMAASGMaintenance AXXXYYY
            345Maintenance ABGHClean brakesXXXYYY
            345Maintenance ADFCClean carXXXYYY
            345Maintenance AHJRIWQRepair xXXXYYY
            345Maintenance A1344Repair YXXXYYY
            347Maintenance BMABSGMaintenance BXXXYYY
            347Maintenance B35531Repair XXXXYYY
            347Maintenance B1847872MaterialsXXXYYY

             

            Hope this helps

            • 3. Re: Classify repair order
              Jennifer VonHagel

              Hi David, is it possible for a single repair order to have multiple Maintenance Types? So would it be possible for Order ID 345 to have Maintenance A and Maintenance B, or will each order get classified only as A, B, C OR D?

               

              Thanks,

              Jennifer

              • 4. Re: Classify repair order
                David Núñez

                Each will have to be classified as A, B, C OR D. They can't have more than one maintenance type, however some orders may not have any maintenance type associated to them, some may only come for repair, which in this case would be classed as "null", right?

                 

                Thanks.

                • 5. Re: Classify repair order
                  Jennifer VonHagel

                  How about something like this? I made up codes for C and D. For Orders with no Maintenance, you can leave it null or put some text in there that makes sense to you.

                   

                  IFNULL(

                      { FIXED [Repair Order] :

                          MAX(IF [Activity Code] = 'MAASG' THEN 'Maintenance A'

                          ELSEIF [Activity Code] = 'MABSG' THEN 'Maintenance B'

                          ELSEIF [Activity Code] = 'MACSG' THEN 'Maintenance C'

                          ELSEIF [Activity Code] = 'MADSG' THEN 'Maintenance D'

                          end)

                  },'No Maintenance')

                   

                  By the way, this works because each order can only have one type of Maintenance. If an order had both A and B for example, the order would be classified as B, or whatever the max letter is.

                   

                  Is this what you're looking for?

                   

                  Thanks,

                  Jennifer

                  • 6. Re: Classify repair order
                    David Núñez

                    Hi Jennifer,

                     

                    Thank you very much, this works great!

                     

                    Take care

                    Best regards,

                    David.