13 Replies Latest reply: Dec 20, 2016 1:03 PM by Eduardo DImperio RSS

    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

        • Re: Join Crash QS
          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.

            • Re: Join Crash QS
              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

                • Re: Join Crash QS
                  Gysbert Wassenaar

                  By dropping it after you have created ANALISE.

                    • Re: Join Crash QS
                      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

                        • Re: Join Crash QS
                          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.

                            • Re: Join Crash QS
                              Eduardo DImperio

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

                                • Re: Join 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?

                                    • Re: Join Crash QS
                                      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?

                        • Re: Join Crash QS
                          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.

                          • Re: Join Crash QS
                            Eduardo DImperio

                            I had an idea,

                            Can i save the result of EXCLUDE_OID_METER into some variable?

                              • Re: Join Crash QS
                                Jonathan Dienst

                                Why not simply this:

                                 

                                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 CONSUMODIA0<>0 AND CONSUMODIA1<>0 AND CONSUMODIA2<>0;

                                 

                                DROP Table ANALISE_TMP;

                                 

                                There is no need to JOIN, and the ORDER BY will also increase the load time, and it serves no purpose here as far as I can see.

                              • Re: Join Crash QS
                                Eduardo DImperio

                                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