8 Replies Latest reply: Apr 20, 2016 1:01 AM by khasimvali shaik RSS

    Filtering data while Calculation

    khasimvali shaik

      Hi Community,

       

      Hope you are doing well.

       

      I am facing issue in my data attached with my QVW file when i am going to filtering and calculation.

      I have columns like

           Branch_Name,

           Complaint_No,

           OpenpendingDate,

           PendingReason,

           Responsible,

           Technician

      The above columns filter data from PendingReason and Technician,

      when PendingReason is REASON NOT AVAILABLE and Technician is TECHNICIAN NOT AVAILABLE then this ALLOCATEBRANCH,

          PendingReason is REASON NOT AVAILABLE and Technician is AVAILABLE  Then This ALLOCATEENGINEER  in Responsible Column.

      How do i achive this?

       

      Regards,

      Khasim.

        • Re: Filtering data while Calculation
          Sunny Talwar

          Not entirely sure what are you trying to do, Would you be able to share the expected output for the sample provided?

            • Re: Filtering data while Calculation
              khasimvali shaik

              Hi Sunny,

               

              Thanks for reply,

              i need to filter data where if (PendingReason is REASON NOT AVAILABLE and Technician is TECHNICIAN NOT AVAILABLE) ,give any name like AllocateBranch and

               

              If(PendingReason is REASON NOT AVAILABLE and Technician is AVAILABLE) then give any name like AllocateEngineer.

              please check attached Excel file along with data. !

               

              Regards,

              Khasim.

                • Re: Filtering data while Calculation
                  Sunny Talwar

                  I am assuming that you are doing this in the pivot table? Do you need this for each row?

                    • Re: Filtering data while Calculation
                      khasimvali shaik

                      Hi,

                       

                      Please check my attached qvw script you would understand,

                      i need to calculate where PendingReason= REASON NOT AVAILABLE and Technician is TECHNICIAN NOT AVAILABLE

                       

                      my requirement is

                      If(PendingReason is REASON NOT AVAILABLE and Technician is AVAILABLE) then give any name like AllocateEngineer.

                        • Re: Filtering data while Calculation
                          Sunny Talwar

                          Check the attached

                           

                          If(PendingReason = 'REASON NOT AVAILABLE' and Technician = 'TECHNICIAN NOT AVAILABLE', 'ALLOCATEBRANCH',

                              If(PendingReason = 'REASON NOT AVAILABLE', 'ALLOCATEENGINEER',

                              if(PendingReason='KITBAG PART PENDING.','ENGINEER',

                             

                              if(PendingReason='BRANCH PART PENDING.','PART PENDING',

                              if(PendingReason='FRANCHISEE/DSC PART PENDING','PART PENDING',

                             

                              if(PendingReason='CALL COMPLETED BUT PENDING FOR CORPORATE APPROVAL','CORPORATE - PSH',

                              if(PendingReason='PENDING FOR GAS CHARGING','ENGINEER',

                              if(PendingReason='PENDING FOR PANEL APPROVAL','CORPORATE - PSH',

                             

                              if(PendingReason='PENDING FOR CALL CANCELLATION','ASI/BSM',

                              if(PendingReason='BCC MAPPING TO BE CORRECTED

                          ','ASI/BSM',

                              if(PendingReason='DENTING AND PAINTING','ENGINEER',

                                 

                              if(PendingReason='FOC REPAIR APPROVED','ENGINEER',

                              if(PendingReason='FOC REPAIR PROPOSAL PENDING FOR COO APPROVAL','CORPORATE - NDC',

                              if(PendingReason='FOC REPAIR PROPOSAL PENDING FOR RM RECOMMENDATION','ASI/BSM',

                              if(PendingReason='FOC REPAIR PROPOSAL TO BE REVISED BY RM','ASI/BSM',

                             

                              if(PendingReason='FOC REPAIR PROPOSAL TO BE REVISED BY SERVICE','ASI/BSM',

                             

                              if(PendingReason='FOC REPAIR REJECTED BY COO','ASI/BSM',

                              if(PendingReason='FOC REPAIR REJECTED BY RM','ASI/BSM',

                              if(PendingReason='LIQUIDATION APPROVED, PENDING FOR ACTUAL LIQUIDATION','ASI/BSM',

                              if(PendingReason='LIQUIDATION PROPOSAL PENDING FOR BCC WORKING','ASI/BSM',

                              if(PendingReason='LIQUIDATION PROPOSAL PENDING FOR COO APPROVAL','CORPORATE - NDC',

                              if(PendingReason='RM MAPPING TO BE CORRECTED','CORPORATE - NDC',

                              if(PendingReason='LIQUIDATION PROPOSAL PENDING FOR RM RECOMMENDATION','ASI/BSM',

                              if(PendingReason='LIQUIDATION PROPOSAL REJECTED','ASI/BSM',

                              if(PendingReason='LIQUIDATION PROPOSAL REJECTED BY BCC','ASI/BSM',

                              if(PendingReason='LIQUIDATION PROPOSAL REJECTED BY RM

                          ','ASI/BSM',

                              if(PendingReason='LIQUIDATION PROPOSAL TO BE REVISED BY BCC','ASI/BSM',

                              if(PendingReason='LIQUIDATION PROPOSAL TO BE REVISED BY RM','ASI/BSM',

                              if(PendingReason='LIQUIDATION PROPOSAL TO BE REVISED BY SERVICE','ASI/BSM',

                              if(PendingReason='PENDING FOR ACTUAL LIQUIDATION OF NON REPAIRABLE SETS, APPROVAL RECEIVED.','ASI/BSM',

                              if(PendingReason='PENDING FOR APPROVAL FROM SALES FOR OUT OF POLICY SET TO BE REPAIRED.','ENGINEER',

                              if(PendingReason='PENDING FOR BCC/CFH APPROVAL FOR NON REPAIRABLE SET LIQUIDATION.','ASI/BSM',

                              if(PendingReason='WITHIN POLICY-CAN BE REPAIRED','ENGINEER',

                             

                              if(PendingReason='PENDING FOR PRODUCT REPLACEMENT','ASI/BSM',

                              if(PendingReason='REFER TO NRN','ASI/BSM',

                             

                              if(PendingReason='RE-OPEN CANCELLED','ENGINEER',

                              if(PendingReason='RE-OPEN CLOSED','ENGINEER',

                              if(PendingReason='SET UNDER REPAIR AT WORKSHOP','ENGINEER',

                             

                              if(PendingReason='REASON NOT AVAILABLE','NOTAVAILABLE',

                             

                              if(PendingReason='ADDRESS NOT TRACEABLE','ENGINEER',

                              if(PendingReason='BILL NOT AVAILABLE','ENGINEER',

                              if(PendingReason='CALL ALLOCATED BUT NOT ATTENDED BY TECHNICIAN','UNP',

                              if(PendingReason='CALL ALLOTTED TO TECHNICIAN','ENGINEER',

                              if(PendingReason='CHASSIS CALL BACK','ENGINEER',

                              if(PendingReason='CUSTOMER REFUSED TO PAY','ENGINEER',

                              if(PendingReason='ESTIMATE GIVEN','ENGINEER',

                              if(PendingReason='FAULT NOT TRACEABLE','ENGINEER',

                              if(PendingReason='HOUSE LOCK','ENGINEER',

                              if(PendingReason='INCOMPLETE DETAIL FOR PDI REPORT GENERATION','ENGINEER',

                              if(PendingReason='NEW DATE GIVEN','ENGINEER',

                              if(PendingReason='NO POWER','ENGINEER',

                              if(PendingReason='PENDING FOR PLUMBING/ELECTRICAL WORK','ENGINEER',

                              if(PendingReason='SET CALLBACK','ENGINEER'))))))))))))))))))))))))))))))))))))))))))))))))))))) as Responsible,