Distinct not working

[eduardo_dimperio]

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);

[surendraj]

Hi

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

--surendra

[eduardo_dimperio]

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

[jonathandienst · Accepted solution]

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.

[eduardo_dimperio]

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.