Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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
May be MaxString(Bucket)
Could you share a sample data?
Not sure if this is what you are after
Interval Match Feature/Function
https://community.qlik.com/search.jspa?q=interval+match&type=post
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.
How do you get min/max values?
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
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
Hi Anwar,
maybe
Calculated Dimension :
=Aggr(Class(Count(DISTINCT [Patient ID]),5,'Nr. Patients'),[Provider ID])
Regards,
Antonio
Min and Max is in the Bucket Table(Please see my original post)