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]

Hi!

I don't know what channel is correct for area. Is there a field in Application_detail_naps with correct area value?

[#3]

Channel could be any. Based on the above example I get following: suppose channel submitted application to RCU but it was wrong area. Then RCU select Appl_refer_Rsc_cde. Then Channel resubmits application to RCMU. Appl_refer_Rsc_cde should be shown only under RCU who selected it not under 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	1

[pokassov]

How I can say RCU is wrong depending on the Application_detail_naps?

[#5]

Can't say wrong only based on Application_detail_naps. Wrong area is one of the many APPL_REFER_Rsc_cde. RCU manually checks either they should process this application or not. If not then they select APPL_REFER_Rsc_cde. After then Channel resubmits to RCMU.

[pokassov]

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 t2;

left join (NAPS_APPL_REFERRAL_REASONS_REF)

load *

resident t2;

drop table t2;

so you can use wrong_cde in expression:

sum(wrong_cde)

or

count({$<wrong_cde={1}>} distinct APPL_REFER_RSN_CDE)

[#7]

i copied and pasted your code in qlikview but qlikview does not read left join etc.

[pokassov]

I didn't check before. I eliminate wrong ','

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 t2;

left join (NAPS_APPL_REFERRAL_REASONS_REF)

load *

resident t2;

drop table t2;

[#9]

I get error that table t1, t2 not found

[pokassov]

Did you load  your tables before? For example NAPS_APPL_REFERRAL_REASONS_REF.

Because

t1:

load

APPL_REFER_RSN_CDE,

CREATED_TMSTMP //IS IT DATETIME?

resident NAPS_APPL_REFERRAL_REASONS_REF;

had to load all rows from NAPS_APPL_REFERRAL_REASONS_REF;

Could you attach you qvw-document?