Join Crash QS

[eduardo_dimperio]

Hi everyone,

I need to get all OID_METERS that dont have value zero during the day, so i did 2 tables, one with all meters and other with meters the get zero in some hour the day.

But when i select excluding that meters with zeros, my QS crashs and i need to close everything anda restar. May someone help me?

//get the meters with zeros

Exclusao:

LOAD

HORA, //key

OID_METER AS EXCLUDE_OID_METER

RESIDENT ANALISE_TMP

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

Right Join(Exclusao)

//get all meters

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,

    CONSUMODIA0,

    CONSUMODIA1,

    CONSUMODIA2,

    HORA //key

    RESIDENT ANALISE_TMP

    WHERE NOT EXISTS(EXCLUDE_OID_METER, OID_METER) // exclude zeros

    ORDER BY OID_METER, HORA DESC;

//crash and burn

[Gysbert_Wassenaar]

Your new table ANALISE will have most fields in common with the table ANALISE_TMP. If you do not drop the table ANALISE_TMP then a very large synthetic key will be calculated. That will probably need so much RAM memory that the Qlik Sense process is terminated by your OS. Or the machine just hangs.

[eduardo_dimperio]

But how can i drop ANALISE_TMP when i using RESIDENT ANALISE_TMP in ANALISE?

And im doing join with Exclusão not with ANALISE_TMP

[Gysbert_Wassenaar]

By dropping it after you have created ANALISE.

[eduardo_dimperio]

Ah sorry, im already doing that, i just not print.

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

HORA,

OID_METER AS EXCLUDE_OID_METER

RESIDENT ANALISE_TMP

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

Right Join(Exclusao)

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,

    CONSUMODIA0,

    CONSUMODIA1,

    CONSUMODIA2,

    HORA

    RESIDENT ANALISE_TMP

    WHERE NOT EXISTS(EXCLUDE_OID_METER, OID_METER)

    ORDER BY OID_METER, HORA DESC;

DROP TABLE ANALISE_TMP;

EXIT SCRIPT

[eduardo_dimperio]

Other information, if i run with this modification, it runs ok, if i show ANALISE_TMP - OID_METER or Exclusao -EXCLUDE_OID_METER on the grafic its instant, but if i try to show both that take long minutes to show.

[Gysbert_Wassenaar]

Ok, then the right join could be creating enormous amounts of records enough to crash Qlik Sense. Every HORA value from ANALYSE will be joined with every record from Exlusao with the same HORA value.

[eduardo_dimperio]

How can i make this work,not show the oid_meters in exclude_oid meter without crash qs?

[Gysbert_Wassenaar]

No idea. I don't understand what you're trying to do. What's the right join supposed to do? Why do you think you need to join ANALISE with Exlusao?

[eduardo_dimperio]

Let explain (or try with my english)

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

For that reason, i thought to put in Exclusao all meters that i get some zero value along the day and after show in ANALISE all meter that not in Exclusao.

To do that exclusion i need join that tables, didnt i?