Field not found

[eduardo_dimperio]

Hi everyone,

Im need to select all oid_meter's that consume was not zero, for that i create a variable that receive the consume zero (Exclusao:)

and select oid_meter not select what's in Exclusao.

Exclusao:

Load

DISTINCT OID_METER AS EXCLUDE_OID_METER,

HORA

RESIDENT ANALISE_TMP

WHERE CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0;

INNER JOIN(Exclusao)

ANALISE:

Load

  HORA,

    ID_LEITURA,

    NAME_SYSTEM,

    NAME_GROUP,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    OID_METER,

    NAME_TYPE_METER,

    TYPE_CONSTANT,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    DATE_READ,

    VALUE_READ,

    CONSUMODIA0,

    CONSUMODIA1,

    CONSUMODIA2,

   EXCLUDE_OID_METER

    RESIDENT ANALISE_TMP

    WHERE OID_METER <> EXCLUDE_OID_METER

    ORDER BY OID_METER;

but i receive this error:

Ocorreram os seguintes erros:

Field not found - <EXCLUDE_OID_METER>

I think the EXCLUDE_OID_METER is already created in exclusao, but maybe the inner join have something to do with.

[eduardo_dimperio]

Yeah, let me explain

The OID_METER.jpg, was a select of all oid_meters

The EXCLUDE_OID_METER.jpg  was a select of all oid_meters with consumodia=0, i print the meters 84065 and 84066 for example with this code:

Exclusao:

LOAD

OID_METER AS EXCLUDE_OID_METER

RESIDENT ANALISE_TMP

WHERE CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0;

After  that i replace that code for your:

Right Join (ANALISE_TMP)

LOAD DISTINCT OID_METER

RESIDENT ANALISE_TMP

WHERE NOT (CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0);

and got the last print OID_METER_RIGHT_JOIN, but the meters 84065 and 84066 still remain.

[eduardo_dimperio]

I did this to show better:

Right Join (ANALISE_TMP)

LOAD DISTINCT OID_METER

RESIDENT ANALISE_TMP

WHERE NOT(CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0);

Teste:

LOAD

OID_METER,

CONSUMODIA0,

CONSUMODIA1,

CONSUMODIA2

RESIDENT ANALISE_TMP

WHERE OID_METER=87066;

DROP TABLE ANALISE_TMP;

This meter should not appear anymore in OID_METER

[sunny_talwar]

I understand your situation now.... Let me think about it a little

ANALISE_TMP:

LOAD

  ID_LEITURA,

    NAME_SYSTEM,

    NAME_GROUP,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    OID_METER,

    NAME_TYPE_METER,

    TYPE_CONSTANT,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    DATE_READ,

    VALUE_READ,

    Date(DATE_READ) AS HORA,

    If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT)  as CONSUMODIA0,

    If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA1,

    If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA2

  RESIDENT TMP2

    ORDER BY OID_METER, DATE_READ DESC;

DROP TABLE TMP,TMP2;

Exclusao:

LOAD DISTINCT OID_METER AS EXCLUDE_OID_METER,

HORA

RESIDENT ANALISE_TMP

WHERE CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0;

INNER JOIN(Exclusao)

ANALISE:

LOAD

  HORA,

    ID_LEITURA,

    NAME_SYSTEM,

    NAME_GROUP,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    OID_METER,

    NAME_TYPE_METER,

    TYPE_CONSTANT,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    DATE_READ,

    VALUE_READ,

    CONSUMODIA0,

    CONSUMODIA1,

    CONSUMODIA2

    RESIDENT ANALISE_TMP

    WHERE NOT EXISTS(EXCLUDE_OID_METER, OID_METER)

    ORDER BY OID_METER, HORA DESC;

[sunny_talwar]

Check the script above, I think I understand your situation now....

[eduardo_dimperio]

lol  this code crashs the QS in the server !

I did 3 tests

With the clause Where and with Inner Join - Crash the system

Without clause where and without Inner Join - Runs Forever

Whitout Inner Join and with clause Where  - Runs ok, but code doesnt work

Any Idea why this happen?

Translate: Connection to the Qlik Sense engine failed for unspecified reasons. Please update your browser or contact your system administrator

[eduardo_dimperio]

I think i found a trick.

I create a flag and will try filter by it

  If(OID_METER=  Peek(OID_METER) and(Peek(VALUE_READ)- VALUE_READ)=0,1,0) as FLAG

but a strange thing is happening

the compiler is not recognizing my table.

ANALISE_TMP:

LOAD

  ID_LEITURA,

    NAME_SYSTEM,

    NAME_GROUP,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    OID_METER,

    NAME_TYPE_METER,

    TYPE_CONSTANT,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    DATE_READ,

    VALUE_READ,

    Date(DATE_READ) AS HORA,

    If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT)  as CONSUMODIA0,

    If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA1,

    If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA2,

    If(OID_METER=  Peek(OID_METER) and(Peek(VALUE_READ)- VALUE_READ)=0,1,0) as FLAG

  RESIDENT TMP2

    ORDER BY OID_METER, DATE_READ DESC;

DROP TABLE TMP,TMP2;

Analise:

LOAD

    ID_LEITURA,

    NAME_SYSTEM,

    NAME_GROUP,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    OID_METER,

    NAME_TYPE_METER,

    TYPE_CONSTANT,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    DATE_READ,

    VALUE_READ,

    HORA,

    CONSUMODIA0,

    CONSUMODIA1,

    CONSUMODIA2,

    FLAG

    RESIDENT ANALISE_TMP

    WHERE FLAG=1

    ORDER BY OID_METER, HORA DESC;

DROP TABLE ANALISE_TMP;

Any idea why?

[rubenmarin · Accepted solution]

Hi, that's because the 2nd table has the same fields than the first table and it's autoconcatenating, use 'NoConcatenate' in the 2nd table:

Analise:

NoConcatenate LOAD

    ID_LEITURA,

    NAME_SYSTEM,

...

[eduardo_dimperio]

You're right, that solve this problem. But my system continuos to crash join. I will close this discuss, because my problem now is other and continuos in other discussion