Filtering data while Calculation

[khasimvali85]

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.

[sunny_talwar]

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

[khasimvali85]

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.

[sunny_talwar]

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

[khasimvali85]

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.

[sunny_talwar]

I think you should def. consider using Mapping Load here. It would be easy to maintain and understand

http://www.learnqlickview.com/a-qlikview-tutorial-mapping-load-in-qlikview/

Using ApplyMap() Function with Mapping Load in QlikView - QlikView Blog, QlikView Videos and Tips at...

[sunny_talwar · Accepted solution]

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]

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

[khasimvali85]

Hi Sunny,

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

Regards,

Khasim.