Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eduardo_dimperi
Valued Contributor II

Set Analysis to get Distinct Field

Hi people,

I have three fields NAME_SYSTEM, OID_GROUP and DATA. I want to count how many NAME_SYSTEM's i have but unfortunately my other fields have more that one value for each NAME_SYSTEM so my count doesn't right.

I tried this, but not worked:  Count(DISTINCT{1-$<DATA={'Nao Agendado'}>}NAME_SYSTEM)

Input:

set_analysis.JPG

Expected Output:

set_analysis2.JPG

1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis to get Distinct Field

If this solved your issue, I would have expected that my suggested expression

Count(DISTINCT {$<DATA -={'Nao Agendado'} >}NAME_SYSTEM)

works as well. Have you tried it?

10 Replies
Highlighted
gladi-cz
Contributor

Re: Set Analysis to get Distinct Field

Hello Eduardo,

Count(DISTINCT NAME_SYSTEM) Doesn't work?


Petr

MVP
MVP

Re: Set Analysis to get Distinct Field

A simple

=Count(DISTINCT NAME_SYSTEM)


should give you the distinct count given your requirement above. It seems you have an additional requirement to filter out some records that don't show a date.

Not quite sure about this requirement, but try with a -= operator in your field modifier.


Count(DISTINCT {$<DATA -={'Nao Agendado'} >}NAME_SYSTEM)



rittermd
Honored Contributor

Re: Set Analysis to get Distinct Field

Why the Set Analysis here?  You didn't describe a reason for it.

Just do a Count (Distinct NAME_SYSTEM)

Partner
Partner

Re: Set Analysis to get Distinct Field

Your question, what you have and what you want to have are so unclear.

Can please clarify what you need with "better words"?

eduardo_dimperi
Valued Contributor II

Re: Set Analysis to get Distinct Field

Hi Petr,

Not work because in my example i have Name_System= Le Quartier Perdizes with OID_GROUP=20, OID_GROUP=23 and more than one Date, the clausule Distinct (far as i know) get distinct row and not distinct field.

So Distinct in that case will return to me 4 elements and not one.

eduardo_dimperi
Valued Contributor II

Re: Set Analysis to get Distinct Field

Hi Stefan, thank you for your time

I need a distinct count for the field and not for the row. So if i use a simple distinct my count will return 4 and not 1

eduardo_dimperi
Valued Contributor II

Re: Set Analysis to get Distinct Field

Hi Mark,

I have some buildings that need inspection, so what i want is count how many buildings already have an inspection scheduled. For that reason i cant take into account multiple dates or other fields, in other words if a have a building named Le Quartier Perdizes with inspection for 4 dates, my count(name_system) output need to be 1 

eduardo_dimperi
Valued Contributor II

Re: Set Analysis to get Distinct Field

Hi, i solved the problema creating another field (a binary one).

Script:

NoConcatenate

SISTEMA:

LOAD

OID_SYSTEM,

    NAME_SYSTEM,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    UF_SYSTEM,

    CEP_SYSTEM,

    OID_GROUP,

    ID_SYSTEM_TYPE,

    If(ISNULL(DATA),'Nao Agendado','Agendado') AS AGENDAMENTO,

    If(ISNULL(DATA),'Nao Agendado',DATE(NUM#(DATA))) AS DATA

RESIDENT TMP;

Chart:

Count(DISTINCT {$<AGENDAMENTO -={'Nao Agendado'} >}NAME_SYSTEM)

MVP
MVP

Re: Set Analysis to get Distinct Field

If this solved your issue, I would have expected that my suggested expression

Count(DISTINCT {$<DATA -={'Nao Agendado'} >}NAME_SYSTEM)

works as well. Have you tried it?