topic Re: Duplication in Values in QlikView

Duplication in Values

Fri, 10 Apr 2015 10:31:45 GMT

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

Re: Duplication in Values

pokassov — Fri, 10 Apr 2015 10:51:31 GMT

Hi!

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

Re: Duplication in Values

Fri, 10 Apr 2015 10:56:18 GMT

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

Re: Duplication in Values

pokassov — Fri, 10 Apr 2015 10:58:43 GMT

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

Re: Duplication in Values

Fri, 10 Apr 2015 11:02:50 GMT

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.

Re: Duplication in Values

pokassov — Fri, 10 Apr 2015 11:12:05 GMT

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)

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

Re: Duplication in Values

Fri, 10 Apr 2015 11:29:04 GMT

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

Re: Duplication in Values

pokassov — Fri, 10 Apr 2015 11:32:09 GMT

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;

Re: Duplication in Values

Fri, 10 Apr 2015 11:39:00 GMT

I get error that table t1, t2 not found

Re: Duplication in Values

pokassov — Fri, 10 Apr 2015 11:47:03 GMT

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?

Re: Duplication in Values

Fri, 10 Apr 2015 11:54:18 GMT

yes it's loading all tables other than t1 t2

Re: Duplication in Values

Fri, 10 Apr 2015 13:35:57 GMT

script as attached

Re: Duplication in Values

pokassov — Fri, 10 Apr 2015 15:52:21 GMT

Why did you write prefix for tables like "DDEWV81B."?

Re: Duplication in Values

Fri, 10 Apr 2015 15:59:29 GMT

i have prefix with all tables but did not mentioned in the attached file. but this is not the issue. i am wondering the script i attached is correct or wrong

Re: Duplication in Values

pokassov — Fri, 10 Apr 2015 16:25:36 GMT

Can you do this?:

1. Settings->Document Properties->General->Generate Logfile (set the checkbox)

2. Reload document

3. Find the logfile in the same folder with document and attach it here.

Re: Duplication in Values

Thu, 16 Apr 2015 08:15:13 GMT

Hi Cepren,

Thanks for your help. If you ignore the prefix then the rest of the script is the same as attached. I still could not sort it out

Re: Duplication in Values

pokassov — Thu, 16 Apr 2015 08:53:20 GMT

Can you do this?:

1. Settings->Document Properties->General->Generate Logfile (set the checkbox)

2. Reload document

3. Find the logfile in the same folder with document and attach it here.

Re: Duplication in Values

Thu, 16 Apr 2015 09:02:51 GMT

as attached

Re: Duplication in Values

pokassov — Thu, 16 Apr 2015 09:09:51 GMT

This is your original script without my advice. Put it together and reload again.

Re: Duplication in Values

Thu, 16 Apr 2015 09:17:02 GMT

it says left join and drop table not found