Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datashaw
Contributor III
Contributor III

Show Distinct ID's in the Dimension

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  

                     

Labels (1)
10 Replies
Edvin
Creator
Creator

How about:

=count(distinct [Account id])

datashaw
Contributor III
Contributor III
Author

No luck on (Count distinct AccountID). 

 

I have tried the below calculated dimension but did not get work

Aggr(count( Distinct AccountID) , DATE)

agigliotti
Partner - Champion
Partner - Champion

why do you need "Account ID" as dimension?

datashaw
Contributor III
Contributor III
Author

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 

Edvin
Creator
Creator

Could you show what you want to achieve?
Because how I understand, 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].
bala_sundar
Contributor III
Contributor III

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.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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

bala_sundar
Contributor III
Contributor III

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,

1.png

And want this to be represented like below,

2.png

Hope it clear.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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.