Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
khasimvali85
Contributor II

Filtering data while Calculation

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.

1 Solution

Accepted Solutions

Re: Filtering data while Calculation

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,

8 Replies

Re: Filtering data while Calculation

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

khasimvali85
Contributor II

Re: Filtering data while Calculation

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

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

khasimvali85
Contributor II

Re: Filtering data while Calculation

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

Re: Filtering data while Calculation

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,

Chanty4u
Esteemed Contributor III

Re: Filtering data while Calculation

awesome sunny...for ur time and patience ......to give the sollution.

khasimvali85
Contributor II

Re: Filtering data while Calculation

Hi Sunny,

Thanks sunny its great and appreciate you, now i am filtering data.

Regards,

Khasim.

Community Browser