topic Re: Show Distinct ID's in the Dimension in App Development

Show Distinct ID's in the Dimension

datashaw — Sat, 16 Nov 2024 04:33:35 GMT

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

Re: Show Distinct ID's in the Dimension

Edvin — Thu, 26 Sep 2019 12:00:41 GMT

How about:

=count(distinct [Account id])

Re: Show Distinct ID's in the Dimension

datashaw — Thu, 26 Sep 2019 12:03:48 GMT

No luck on (Count distinct AccountID).

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

Aggr(count( Distinct AccountID) , DATE)

Re: Show Distinct ID's in the Dimension

agigliotti — Thu, 26 Sep 2019 12:04:44 GMT

why do you need "Account ID" as dimension?

Re: Show Distinct ID's in the Dimension

datashaw — Thu, 26 Sep 2019 15:13:22 GMT

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

Re: Show Distinct ID's in the Dimension

Edvin — Fri, 27 Sep 2019 11:56:35 GMT

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].

Re: Show Distinct ID's in the Dimension

bala_sundar — Fri, 14 Aug 2020 12:59:47 GMT

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.

Re: Show Distinct ID's in the Dimension

stevedark — Sat, 15 Aug 2020 15:43:29 GMT

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

Re: Show Distinct ID's in the Dimension

bala_sundar — Mon, 17 Aug 2020 05:22:26 GMT

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.

Re: Show Distinct ID's in the Dimension

stevedark — Mon, 17 Aug 2020 06:24:11 GMT

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.

Re: Show Distinct ID's in the Dimension

bala_sundar — Mon, 17 Aug 2020 07:26:21 GMT

Thanks Steve, the Subfield function works and helped me to achieve my objective.