Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
eduardo_dimperi
Valued Contributor II

Distinct not working

Hi everyone,

I really dont get it why my distinct and my not exist doesnt work.

When i use distinct (image1), im still seeing two value 87066, and when i use not exists (image 2), i still have 87066 value.

Why?

Exclusao:

LOAD

DISTINCT OID_METER,

          EXCLUDE_METER

RESIDENT ANALISE_TMP

WHERE NOT EXISTS (OID_METER,EXCLUDE_METER);

Distinct Error.JPGNot Exists.JPG

1 Solution

Accepted Solutions
MVP
MVP

Re: Distinct not working

Its not completely clear what you are trying to do, but the script that you provided loaded the first line because the first record is distinct by definition and nothing exists yet so the where clause will be true. The second line is also distinct (because distinct in this context means a distinct line, all the records), and will get loaded because EXCLUDE_METER is null, so the exists evaluates to false and the where clause will be WHERE NOT FALSE (always true).

Perhaps a clear explanation of what you are trying to do would help.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
surendraj
Valued Contributor

Re: Distinct not working

Hi

please check how these values are internally stored in qlikview either in text or number format.

--surendra

eduardo_dimperi
Valued Contributor II

Re: Distinct not working

The not Exists i found the error, i change the order of function

WHERE NOT EXISTS (OID_METER,EXCLUDE_METER);

WHERE NOT EXISTS (EXCLUDE_METER,OID_METER);


But still don't get it the distinct

MVP
MVP

Re: Distinct not working

Its not completely clear what you are trying to do, but the script that you provided loaded the first line because the first record is distinct by definition and nothing exists yet so the where clause will be true. The second line is also distinct (because distinct in this context means a distinct line, all the records), and will get loaded because EXCLUDE_METER is null, so the exists evaluates to false and the where clause will be WHERE NOT FALSE (always true).

Perhaps a clear explanation of what you are trying to do would help.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
eduardo_dimperi
Valued Contributor II

Re: Distinct not working

Hi Jonathan, you are right, now i understant the concept.

I'll explain using a example of another discuss:

I have some meters and they register water consume every hour. If some meter register some zero value, i need to show no more that meter, even if this meter register some value after that zero. like this example below:

Input

Meter  Value Hour

123      10    1:00am

123      15    2:00am

123      00    3:00am

123      15    4:00am

345      12    1:00am

345      17    2:00am

345      05    3:00am

345      30    4:00am


Output

Meter  Value Hour

345      12    1:00am

345      17    2:00am

345      05    3:00am

345      30    4:00am

I try use this code, that seeing very simple, but for some reason that no one knows, that crash my QS.

ANALISE:

LOAD

  OID_METER,

    ID_LEITURA,

    NAME_SYSTEM,

    NAME_GROUP,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    NAME_TYPE_METER,

    TYPE_CONSTANT,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    DATE_READ,

    VALUE_READ,

    CONSUMODIA0,

    CONSUMODIA1,

    CONSUMODIA2,

    HOUR(HORA) as HORA_2,

    EXCLUDE_METER

    RESIDENT ANALISE_TMP

       WHERE NOT EXISTS (EXCLUDE_METER,OID_METER)

    ORDER BY OID_METER,HORA DESC;

So now im use a short version, to get this OID_Meters where not in Exclude_oid meters and i'll try a inner join with analise after that, just because the code above is crashing for no reason.