Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi!
I don't know what channel is correct for area. Is there a field in Application_detail_naps with correct area value?
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 |
How I can say RCU is wrong depending on the Application_detail_naps?
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.
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)
i copied and pasted your code in qlikview but qlikview does not read left join etc.
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;
I get error that table t1, t2 not found
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?