topic Re: Distinct Dimension with calculated column in QlikView

Distinct Dimension with calculated column

subhohere — Sat, 16 Nov 2024 20:08:15 GMT

Hi Qlik Gurus,

I got stuck in a problem which I am trying to fix and not able to achieve. I want to filter out the data based on a particular dimension (i.e Dec in the table). I want to take the highest 'Dec' value and shown it in the chart. I want to achieve this UI level in calculated dimension. Basically want to remove the line item with low Dec value and take the max(dec) value.

I have achieved some part where the Dec value is different for same ID the result is correct taking the max of Dec and getting one line item,.

Ex: For ID 11234 I am getting the first line item where Dec is 9.

But problem started where both 'Dec' values are same. Code is not working and throws out both line items. for ID 93451 & 98735.

I have to achieve this in UI level as per client requirement and cannot make changes in script level. Can anyone please help me out. Below is my expression I have used in calculated column.

if(Dec=Aggr(max(Dec),[ID]),Dec)

So, for each ID we will have only one line item with the highest Dec value or if Dec value is same we will have only one distinct line item.

ID	First name	Last name	Address	City	Dec
11234	John	Martin	Street	TX	9
11234	John	Martin	Street	TX	5
93451	Alice	osman	Street	IL	8
93451	Alice	osman	Street	IL	8
67891	Richard	Rap	Street	TN	4
98735	Jessica	Sheey	Street	OH	7
98735	Jessica	Sheey	Street	OH	7
78901	Sara	Starford	Street	IL	2
78901	Sara	Starford	Street	IL	4
15678	Maria	Span	Street	TX	2

Re: Distinct Dimension with calculated column

Vegar — Thu, 05 Sep 2019 12:29:55 GMT

Try this expression:

=MAX(total <ID> Dec)

Re: Distinct Dimension with calculated column

sunny_talwar — Thu, 05 Sep 2019 12:37:23 GMT

What other dimensions do you have in your chart... do any of those dimensions have different value for 93451 & 98735? Dimensions will always show unique values based on the combination of dimensions you have... if all the dimensions have the same value, they should not repeat....

Re: Distinct Dimension with calculated column

subhohere — Thu, 05 Sep 2019 12:39:56 GMT

The data provided by me is dummy data. When I applied this is real client qvw I am still getting error in calculated column. The table is straight table where I am using your code for the calculated column.

Re: Distinct Dimension with calculated column

subhohere — Thu, 05 Sep 2019 12:42:55 GMT

Sunny,

Thanks for replying so quickly. I have quite few dimensions in the straight table. I want to achieve a distinct record based on the Max(Dec) value.

Re: Distinct Dimension with calculated column

sunny_talwar — Thu, 05 Sep 2019 12:53:15 GMT

That make sense, but did my question make sense? If you have a dimension called Zip Code for example which doesn't have the same value.... Let's say 93451 have two rows with Zip Code 111001 and 111002 and you add Zip Code as a dimension.... then you will see two rows... because both the rows still have Dec = 8... What would you want to see for Zip Code if this happens? Randomly show 1? Show nothing? Show concatenated value?

Re: Distinct Dimension with calculated column

subhohere — Wed, 11 Sep 2019 15:41:18 GMT

ok I got it. All other column values for duplicates are same except the Dec value. It was showing error. But I got the code now, got it working.

I have created a calculated column and it's working fine except one. But the problem is for one ID it's not taking the if condition and getting suppressed due to ticking 'Suppress Null value'. It is taking null. But Dec has got 5 & 8. When I am taking the max of 'Dec' in text box for this particular id it's showing up correctly. Not sure what is the character in there for the Dec value.

Can you please tell a workaround for this?

Re: Distinct Dimension with calculated column

sunny_talwar — Wed, 11 Sep 2019 16:14:31 GMT

Would you be able to share a dashboard where we can see this issue?