Duplication in Values

[#1]

Hi,

I have attached Table view. Application_detail_naps has channel field which includes e.g. branch, phone etc. Naps_Appl_Referral_Reasons table has Appl_Refer_Rsc_Cde field which incudes different rework reasons. Naps_Appl_Workflow_History table has Area which includes three options RCU, CU & RCMU. I need to show how many applications are submitted from each Channel against each Area and how many Appl_Refer_Rsc_Cde logged in each Area.

Application should go only to one Area from Channel but in some cases if applications goes to wrong area then Appl_Refer_Rsc_Cde is/are selected and then channel resubmitted to correct Area. Currently qlikview is showing Appl_Refer_Rsc_Cde under both areas (one is wrong area who selected Appl_Refer_Rsc_Cde and second correct area where resubmitted.) Appl_Refer_Rsc_Cde should be only shown under wrong area who selected it. e.g. application moved in RCU which is wrong area then RCU selects a Appl_Refer_Rsc_Cde channel under Application_Detail_naps resubmits to correct area e.g. RCMU. Currently Appl_Refer_Rsc_Cde is shown under both RCU and RCMU however it should be only under RCU not RCMU.

Regards,

Shoaib

[pokassov]

It was my mistake. I bolded wrong line in script.

t1:

load

APPL_REFER_RSN_CDE,

CREATED_TMSTMP //IS IT DATETIME?

resident NAPS_APPL_REFERRAL_REASONS_REF;

left join (t1)

load APPL_REFER_RSN_CDE,

     APPL_ID

resident NAPS_APPL_REFERRAL_REASONS;

t2:

load

     APPL_REFER_RSN_CDE,

     if(previous(APPL_REFER_RSN_CDE)=APPL_REFER_RSN_CDE,1,0)     as wrong_cde

resident t1

order  by APPL_ID,

     CREATED_TMSTMP desc;

drop table t1;

left join (NAPS_APPL_REFERRAL_REASONS_REF)

load *

resident t2;

drop table t2;

[#3]

sctip loaded susccesfully but if i sum(wrong_cde) then this logic is not working. It does not change with Area

[#4]

Thanks but Channel submits application to Area then Area selects rework reasons (if required). If rework reason(s) is selected then Channel resubmits application to the same area. In this case I am getting correct stats. Only the issue is that time when Channel submits application to wrong Area then they select rework reasons that it should not have sent to them. Then Channel submits to correct area. In this case I am getting rework reason under both correct and wrong area however in reality it should be under that wrong area who selected rework reasons.

e.g. application moved in RCU which is wrong area then RCU selects a Appl_Refer_Rsc_Cde.  After this Channel under Application_Detail_naps resubmits application to correct area e.g. RCMU. Currently Appl_Refer_Rsc_Cde is showning under both RCU and RCMU however it should be only under RCU not RCMU.

APPL_REFER_Rsc_cde	Area	Total Reasons
Referred to incorrect credit decisioning centre	RCU	1
Referred to incorrect credit decisioning centre	RCMU	1
Total Applications	Total Applications

[#5]

your logic did not work . Please find attached screenshot. Appl_Refer_Desc was selected in RCU not in RCMU. But my logic is showing it under RCU & RCMU. I want to show it under only RCU