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);
Hi
please check how these values are internally stored in qlikview either in text or number format.
--surendra
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
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.
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.