topic Re: Set Analysis - Get unique records in QlikView

Set Analysis - Get unique records

Mon, 08 Jun 2015 19:12:54 GMT

Hi guys

So in the table below, we have a maximum of 2 attempts to get a successful transaction per policy ID but not all first attempt failures are retried. I am trying to create a unique view that shows me distinct count of Policy ID's that were successful or unsuccessful as show in the second table. Please note that selections can still be made on the Success Flag on the sheet.

Policy ID	Attempt Number	Success Flag
1	1	No
1	2	Yes
2	1	No
3	1	No
3	2	No
4	1	Yes
5	1	No

Unsuccessful	Successful	Total
3	2	5

Your help will be greatly appreciated.

Re: Set Analysis - Get unique records

ramoncova06 — Mon, 08 Jun 2015 20:50:31 GMT

see the attached file

my assumption was that you only wanted to exclude the ones that did not had a success flag

Re: Set Analysis - Get unique records

Mon, 08 Jun 2015 21:12:14 GMT

Thanks Ramon . You are a genius and you just saved my life. Quick question: Would it be possible to create one expression and use the Success Flag as a dimension in a table? I need to do this for 6 different metrics and that would mean creating 18 expressoins

Re: Set Analysis - Get unique records

maxgro — Mon, 08 Jun 2015 21:17:24 GMT

successful

sum(aggr(count({$ <[Success Flag]={Yes}>} distinct [Success Flag]),[Policy ID]))

total

count(DISTINCT [Policy ID])

unsuccessful

total - successful

Re: Set Analysis - Get unique records

ramoncova06 — Mon, 08 Jun 2015 21:48:26 GMT

you mean something like this ?

Re: Set Analysis - Get unique records

Tue, 09 Jun 2015 07:56:45 GMT

That's excellent!! Thanks a mil.

Re: Set Analysis - Get unique records

Tue, 09 Jun 2015 10:16:21 GMT

Hi Ramon

So it gets a little more complicated when I include the periods and types. We have an overlap of policies which should count for both successful and unsuccessful per period. Also the successful policies from [Policy ID] =E({1<[Success Flag] = {'Yes'}>}) should also consider that specific period in the dimension instead of the entire data set. So, with the following set of data:

load * Inline

[Period , Policy ID , Type , Attempt Number , Success Flag

A , 1 , X , 1 , No

A , 1 , X , 2 , Yes

A , 1 , Y , 1 , Yes

A , 2 , Y , 1 , No

A , 3 , Y , 1 , No

A , 3 , Y , 2 , No

A , 3 , X , 1 , Yes

A , 4 , X , 1 , Yes

A , 5 , X , 1 , No

B , 1 , X , 1 , No

B , 1 , X , 2 , Yes

B , 1 , Y , 1 , Yes

B , 2 , Y , 1 , Yes

B , 3 , Y , 1 , No

B , 3 , Y , 2 , No

B , 3 , X , 1 , Yes

B , 4 , X , 1 , Yes

B , 5 , X , 1 , No];

This should result in the following tables :

Period	Success Flag	Metric
		5
A	Yes	3
A	No	3
B	Yes	4
B	No	2

Period	Success Flag	Metirc
		4
A	Yes	3
B	Yes	4

Period	Success Flag	Metric
		2
A	No	3
B	No	2

I really appreciate your help.

Re: Set Analysis - Get unique records

ramoncova06 — Tue, 09 Jun 2015 15:07:39 GMT

the easiest thing to do is create a combination of the policy + period and then filter based on that