Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to show distinct Account ID's in the straight table dimention.
my dimensions are Account ID, Country , Date
Expression: 1. Sum(Amount)
2. Count(Account ID)
i'm expecting the result with the distinct AccountID in the dimension. but i am getting duplicate ID's would you please resolve the issue.
I do not have access to edit the data model or script only access on frontend in qlik sense
How about:
=count(distinct [Account id])
No luck on (Count distinct AccountID).
I have tried the below calculated dimension but did not get work
Aggr(count( Distinct AccountID) , DATE)
why do you need "Account ID" as dimension?
User will export the data into excel, for the analysis purpose they need the each ID level. So must use the ID in dimension, appreciate, is there any other way of doing this
Hi Edvin,
I have a same scenario where my [Account ID] is getting duplicated for each [Country], in this case can i convert the country values into column like [Country1], [Country2] and so on? so that my [Account ID] wont be duplicated.
The default functionality in Qlik is that only unique values show in dimensions.
If you have a chart with only Account ID as a dimension and then two dimensions do you get duplicate Account IDs?
If you create a filter pane with Account ID in there do you see duplicates?
If you select an Account ID that is appearing more than once, does it select both values or just one?
If duplicates are showing in Filter Panes, then it will be because the values are not identical. Things that can cause seemingly identical items to not be identical are capitalisation, spaces, date formatting and dual datatypes.
What sort of data type is your Account ID? Can you post an image of the duplicates?
Steve
I am just using a table, no charts. No duplicates found on filter pane. Yes, if i select a account ID, it selects both in table. Account ID is INT.
I understand your one of the above replies that either for each [Account ID] there is more than one [Country] or [Date], so it's normal that [Account ID] is duplicating for each unique [Country] or [Date].
So my doubt is, in a table, repeated entries of [Account ID] for each [Country], in this case can i convert the country values into single row and column like [Country1], [Country2] and so on? so that my [Account ID] wont be repeated.
I have a table like this,
And want this to be represented like below,
Hope it clear.
The simplest way will be to remove Country as a dimension and then add it back in as a measure, with the following code:
=CONCAT(DISTINCT Country, ', ')
This will give you a comma separated list of countries in that column. The same can be done for dates if you wish.
Where this is less advantageous is that you will not be able to search or select on the Country field from within the table.
If separate columns are a must then you can use the following, with the integer changing for each column:
=SUBFIELD(CONCAT(DISTINCT Country, ', '), 1)
This is adding extra processing though, without any additional information being given back to the user (it will also make your table wider overall).
To have things so that they are searchable and selectable from the table you will want to create the comma separated list, and the separate columns in the load script, using a RESIDENT load and a GROUP BY. Let me know if that is required.