topic Show Distinct String values in QlikSense table ? in App Development

Show Distinct String values in QlikSense table ?

S1m55r — Sat, 16 Nov 2024 03:37:25 GMT

Version - Qlik Sense Nov 2018

I have table with multiple columns with different datatypes.

In this table i have string based column field with many duplicate values. I wish to show only the Distinct or unique values.

This table i'm going to distribute via N printing as Excel output for further analysis.

Distinct function is not working here. i also tried this formula "=concat(distinct(myfield), ',')" this was referred from another old post.

I wish to do this in Qlik sense only.

Any thoughts ?

@sunny_talwar

Re: Show Distinct String values in QlikSense table ?

jaibau1993 — Fri, 10 Jan 2020 08:08:15 GMT

Hi!

That "=concat(distinct(myfield), ',')" should work... can you share a sample APP os a screenshot?

Regards,

Jaime.

Re: Show Distinct String values in QlikSense table ?

S1m55r — Fri, 10 Jan 2020 12:54:55 GMT

Hello Jaime,

Thank you for your inputs. I did tried as below with Category as field but got invalid dimension as error.

=concat(distinct(Category),',')

let me know,

Thanks

Re: Show Distinct String values in QlikSense table ?

jaibau1993 — Fri, 10 Jan 2020 13:13:39 GMT

Hi!

You are using it as a dimension but it should be used as an expression.

But this made me think. If you are using Category as a dimension then this value is repeated because of some other dimensions. For example, if you create a straight table with Category and Product as Dimensions and Sum(Sales) as Expression, then Category values will appear as many times as different products there are in that categories.

If you don't solve your problem, try to give us more details about the table you are triyng to create (dimensions and expressions you want it to have)

Regards!

Jaime

Re: Show Distinct String values in QlikSense table ?

Kushal_Chawda — Fri, 10 Jan 2020 14:16:01 GMT

try this

=concat ( distinct total (Category),',')

Re: Show Distinct String values in QlikSense table ?

S1m55r — Mon, 13 Jan 2020 10:18:58 GMT

Hello @jaibau1993 ,

This is a simple table, i'm not doing any calculation here.

I just wanted to distribute this table in N-printing after filtering some values on numerical fields.

Thanks

Re: Show Distinct String values in QlikSense table ?

S1m55r — Mon, 13 Jan 2020 13:08:19 GMT

@Kushal_Chawda

Your solution is giving me a long string with concat

Re: Show Distinct String values in QlikSense table ?

Kushal_Chawda — Mon, 13 Jan 2020 14:48:51 GMT

What is the expected output?

Re: Show Distinct String values in QlikSense table ?

S1m55r — Tue, 14 Jan 2020 04:58:42 GMT

Hello @Kushal_Chawda,

I wanted to create a simple table without duplicate values in category column.

Just like Excel table with rows and columns.

E.g. In Qlik table i have added few different dimensions as a column. In category column i have multiple duplicate values. I want a table without duplicate category values. First/any category from the column should be taken and other rows should be ignored.

Thank you

Table Now

Table expected

Category 1 and 2 duplicates should be removed.

Thank you @Kushal_Chawda

Re: Show Distinct String values in QlikSense table ?

Kushal_Chawda — Tue, 14 Jan 2020 12:05:28 GMT

Hi, what is the rule to remove duplicate? Here though there are duplicates for category, but there are different region associated with that. On what basis you decide which category with region you need to keep

Re: Show Distinct String values in QlikSense table ?

S1m55r — Tue, 14 Jan 2020 12:20:42 GMT

@Kushal_Chawda primary focus here is to get unique value of Category in the table.

So for this particular table and requirement my other columns are irrelevant for me. Any region or product or other dimension would be fine but the category should be distinct.

Re: Show Distinct String values in QlikSense table ?

sprutskih — Tue, 04 Aug 2020 13:50:29 GMT

Hello!

Did you found solution? I have exactly the same task. How to use Distinct in table visualization?

Re: Show Distinct String values in QlikSense table ?

Kushal_Chawda — Tue, 04 Aug 2020 13:54:43 GMT

Use maxstring(Region) as expression

Re: Show Distinct String values in QlikSense table ?

Kushal_Chawda — Tue, 04 Aug 2020 13:56:51 GMT

If you want to do it in script then use below

Data:

LOAD Product,

Category,

maxstring(Region) as Region

FROM Source

Group by Product,Category;

Re: Show Distinct String values in QlikSense table ?

sprutskih — Tue, 04 Aug 2020 14:34:09 GMT

hello!

I need to get only unique rows exactly in table chart, not in load script.

=maxstring(category) not work for me in table chart dimension field( qlik sense give error). May be I need to use some other field to insert =maxstring(category)?

Re: Show Distinct String values in QlikSense table ?

Kushal_Chawda — Tue, 04 Aug 2020 14:37:38 GMT

You need to use this formula in measure.

Re: Show Distinct String values in QlikSense table ?

sprutskih — Tue, 04 Aug 2020 14:54:43 GMT

I create measure "UNIQ CATEGORY" with formula maxstring(category) and I delete dimension category.

But this solution not work. I still have duplicated rows with the same category in the field "UNIQ CATEGORY"