Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
eduardo_dimperi
Not applicable

Join Crash QS

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

1 Solution

Accepted Solutions
eduardo_dimperi
Not applicable

Re: Join Crash QS

Hey Guys,

Follow the final code:

Exclusao:

LOAD

  OID_METER,

  EXCLUDE_METER

RESIDENT ANALISE_TMP

WHERE NOT EXISTS (EXCLUDE_METER,OID_METER);

INNER JOIN (Exclusao)

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,

    If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA0)- CONSUMODIA0) AS CONSUMO_REAL0,

    If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA1)- CONSUMODIA1) AS CONSUMO_REAL1,

    If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA2)- CONSUMODIA2) AS CONSUMO_REAL2,

    HORA

    RESIDENT ANALISE_TMP

//    WHERE  (CONSUMODIA0>=0.005 OR CONSUMODIA1>=0.005 OR CONSUMODIA2>=0.005)

    where (HOUR(HORA)=2 OR HOUR(HORA)=5)

    ORDER BY OID_METER DESC;

Thanks for help

13 Replies
Gysbert_Wassenaar
Not applicable

Re: Join Crash QS

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.


talk is cheap, supply exceeds demand
eduardo_dimperi
Not applicable

Re: Join Crash QS

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
Not applicable

Re: Join Crash QS

By dropping it after you have created ANALISE.


talk is cheap, supply exceeds demand
eduardo_dimperi
Not applicable

Re: Join Crash QS

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_dimperi
Not applicable

Re: Join Crash QS

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
Not applicable

Re: Join Crash QS

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.


talk is cheap, supply exceeds demand
eduardo_dimperi
Not applicable

Re: Join Crash QS

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

Gysbert_Wassenaar
Not applicable

Re: Join Crash QS

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?


talk is cheap, supply exceeds demand
eduardo_dimperi
Not applicable

Re: Join Crash QS

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?