Set Analysis to get Distinct Field

[eduardo_dimperio]

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:

Expected Output:

[gladi-cz]

Hello Eduardo,

Count(DISTINCT NAME_SYSTEM) Doesn't work?

Petr

[swuehl]

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]

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

Just do a Count (Distinct NAME_SYSTEM)

[OmarBenSalem]

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

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

[eduardo_dimperio]

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

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

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

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)

[swuehl · Accepted solution]

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?