-
Re: Distinct not working
surendra j Dec 20, 2016 8:04 AM (in response to Eduardo DImperio)Hi
please check how these values are internally stored in qlikview either in text or number format.
--surendra
-
Re: Distinct not working
Eduardo DImperio Dec 20, 2016 8:11 AM (in response to 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
-
Re: Distinct not working
Jonathan Dienst Dec 20, 2016 8:13 AM (in response to Eduardo DImperio)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.
-
Re: Distinct not working
Eduardo DImperio Dec 20, 2016 8:26 AM (in response to Jonathan Dienst )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.
-