Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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....
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.
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.
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?
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?
Would you be able to share a dashboard where we can see this issue?