topic Re: Compare Min/Max for Bucket in QlikView

Compare Min/Max for Bucket

Anonymous — Thu, 17 Aug 2017 11:04:52 GMT

We have requirement for comparing a aggregated value on front end compare it with Min and Max and find the bucket(this bucket would be the Dimension for a chart)

Bucket Table would be

Row Num	Min	Max	Bucket
1	0	5	0-5
2	6	10	6-10
3	11	20	11-20

And Value aggregated on front end would be compared with min and max and Bucket should be selected e.g. value is 3 than 0-5 bucket would be selected.

This Bucket would be used as dimension in a chart.

Please advise how this can be implemented.

Regards,

ASP

Re: Compare Min/Max for Bucket

Anil_Babu_Samineni — Thu, 17 Aug 2017 11:06:56 GMT

May be MaxString(Bucket)

Re: Compare Min/Max for Bucket

tresB — Thu, 17 Aug 2017 11:24:59 GMT

Could you share a sample data?

Re: Compare Min/Max for Bucket

vinieme12 — Thu, 17 Aug 2017 12:45:43 GMT

Not sure if this is what you are after

Interval Match Feature/Function

https://community.qlik.com/search.jspa?q=interval+match&type=post

Re: Compare Min/Max for Bucket

Anonymous — Thu, 17 Aug 2017 15:44:29 GMT

Row Num	Provider ID	Patient ID
1	AA	11
2	AA	12
3	AA	13
4	BB	21

So I want to Count Patient per Provider e.g aggr(count(Patient),Provider) from above AA has 3 Patients.

Referring to Bucket table I should get “0-5” as 3>0(Min Value) and 3<5(Max Value),This will be Dimension

Now Expression would be how many Provider in each bucket. So “0-5” bucket has 1 Provider

I can write if Statement with hardcode values but client wants it dynamic, with just values in table changed buckets should change.

Re: Compare Min/Max for Bucket

tresB — Fri, 18 Aug 2017 04:37:43 GMT

How do you get min/max values?

Re: Compare Min/Max for Bucket

passionate — Fri, 18 Aug 2017 05:25:57 GMT

Hi Anwar,

The best practice to achieve this is at load script.

You can use interval match to this in load script.

Is there any specific reason you want to do this on front end?

Regards,

Pankaj

Re: Compare Min/Max for Bucket

passionate — Fri, 18 Aug 2017 05:32:48 GMT

Hi Anwar,

This will help you:

Sample:

Left join Table

Load Count([Patient ID]) as CountPatientId,[Provider ID]

Resident Table //Table is name of table in which patientid and provider id is present

Group by [Provider ID]

//This will give one more column in Table which is count of PatientID that you are looking.

Left join (Sample)

intervalmatch(CountPatientId)

Load

Min,

Max

Resident;

Left join

Load * Resident

Table1; //Table is name of table in which min and max interval is present

Regards,

Pankaj

Re: Compare Min/Max for Bucket

antoniotiman — Fri, 18 Aug 2017 05:42:07 GMT

Hi Anwar,

maybe

Calculated Dimension :

=Aggr(Class(Count(DISTINCT [Patient ID]),5,'Nr. Patients'),[Provider ID])

Regards,

Antonio

Re: Compare Min/Max for Bucket

Anonymous — Fri, 18 Aug 2017 09:01:27 GMT

Min and Max is in the Bucket Table(Please see my original post)

Re: Compare Min/Max for Bucket

Anonymous — Fri, 18 Aug 2017 09:02:29 GMT

Antonio,

5 you have mentioned in aggregation is static, the Band can be 0-5,6-10,11-20

Re: Compare Min/Max for Bucket

Anonymous — Fri, 18 Aug 2017 09:07:10 GMT

Yes Because Aggregation will happen only on Front end i cannot count and bring the Value as it will restrict slice and dice

Re: Compare Min/Max for Bucket

antoniotiman — Fri, 18 Aug 2017 09:13:40 GMT

5 isin't static. Class Aggregates as You want.

Add Value to Data and try.

Re: Compare Min/Max for Bucket

antoniotiman — Fri, 18 Aug 2017 09:17:43 GMT

See below Example

Load * Inline [
Row Num,Provider ID,Patient ID
1,AA,11
2,AA,12
3,AA,13
4,BB,21
5,CC,1
6,CC,2
7,CC,3
8,CC,4
9,CC,5
10,CC,6
11,CC,7
];

Re: Compare Min/Max for Bucket

Anonymous — Fri, 18 Aug 2017 10:10:08 GMT

No I Mean my requirement is as Below

Bucket should be 0-5, 6-10, 11-20(here it is band of 10), so how would i have band of 5, 10 and 25 in single expression.

Also cannot write nested If as solution should be Dynamic, if new Client comes Band should be according to that client which is configured in Bucket Table, we can also change Structure of Bucket Table that's something we designed for our requirement.

Re: Compare Min/Max for Bucket

antoniotiman — Fri, 18 Aug 2017 11:05:02 GMT

See Attachment

Re: Compare Min/Max for Bucket

Anonymous — Fri, 18 Aug 2017 11:50:49 GMT

This is good but if i have another band 20-35(Band of 15) we need to have nested If or there is any other solution without that

Re: Compare Min/Max for Bucket

antoniotiman — Fri, 18 Aug 2017 12:02:08 GMT

See Attachment

Re: Compare Min/Max for Bucket

Anonymous — Mon, 21 Aug 2017 05:45:47 GMT

Toni, thanks for your help, but this is what i had done initially but issue is this becomes static, now if i have another client and there Band is different than this logic wouldn't work, based on which client is looking at report Band should change, thats why we had created the Bucket Table where we inserted Band for Each client, but i was not able to compare to Min and Max(Same If condition you wrote).

Re: Compare Min/Max for Bucket

effinty2112 — Mon, 21 Aug 2017 09:49:50 GMT

Hi Anwar,

Maybe a calculated dimension like:

=Aggr(

Pick(

Match(-1,

Count(DISTINCT [Patient ID]) <=5,

Count(DISTINCT [Patient ID]) <=10,

Count(DISTINCT [Patient ID]) <=20),

'0-5',

'6-10',

'11-20')

,[Provider ID])

Good luck

Andrew