topic Re: Filtering data while Calculation in QlikView

Filtering data while Calculation

khasimvali85 — Tue, 19 Apr 2016 11:07:26 GMT

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 — Tue, 19 Apr 2016 11:11:51 GMT

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

khasimvali85 — Tue, 19 Apr 2016 11:22:02 GMT

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 — Tue, 19 Apr 2016 11:24:17 GMT

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

Re: Filtering data while Calculation

khasimvali85 — Tue, 19 Apr 2016 11:31:37 GMT

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 — Tue, 19 Apr 2016 11:34:54 GMT

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 QlikShare

Re: Filtering data while Calculation

sunny_talwar — Tue, 19 Apr 2016 11:39:57 GMT

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,

Re: Filtering data while Calculation

Chanty4u — Tue, 19 Apr 2016 14:04:30 GMT

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

Re: Filtering data while Calculation

khasimvali85 — Wed, 20 Apr 2016 05:01:17 GMT

Hi Sunny,

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

Regards,

Khasim.