Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eduardo_dimperio
Specialist II
Specialist II

Problem with Left Join

Hi !

I need a little help to understand why i can not still use this code.

My code works, but last month my data had increase and my code pass to process from 2 minutes to 5 hours and consumo a lot of memory, but my data not increase that much. All problem occur in the last left join.

We talk about 5 milions of lines (Table Exclusao) to left join with 140 thousand lines, that i think is a small table to QlikSense

Any ideia why or how i can workaround this?

Exclusao:

LOAD

    OID_METER,

    EXCLUIR

    RESIDENT Exclude_Meter

    WHERE NOT EXISTS(EXCLUIR,OID_METER)=-1

    ORDER BY EXCLUIR;

   

DROP TABLE Exclude_Meter;

   

inner join (Exclusao)

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,

    DATA AS DIA0,

    TEMPO,

    VALUE_READ AS Val0,

    IF(OID_METER=PEEK(OID_METER),PEEK(Val0)*PEEK(TYPE_CONSTANT)-VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA0

  RESIDENT TMP2

    where FLOOR(DATA)=FLOOR(TODAY()) AND (HOUR(TEMPO)=2 OR HOUR(TEMPO)=5)

    ORDER BY OID_METER,DATA,TEMPO DESC ;

LEFT JOIN (Exclusao)

LOAD

  OID_METER,

    TYPE_CONSTANT,

    VALUE_READ AS Val1,

    IF(OID_METER=PEEK(OID_METER),PEEK(Val1)*PEEK(TYPE_CONSTANT)-VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA1

  RESIDENT TMP2

    where FLOOR(DATA)=FLOOR(TODAY()-1) AND (HOUR(TEMPO)=2 OR HOUR(TEMPO)=5)

    ORDER BY OID_METER,DATA,TEMPO DESC ;

//LEFT JOIN (Exclusao)

LOAD

  OID_METER,

    TYPE_CONSTANT,

     VALUE_READ AS Val2,

    IF(OID_METER=PEEK(OID_METER),PEEK(Val2)*PEEK(TYPE_CONSTANT)-VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA2

  RESIDENT TMP2

    where FLOOR(DATA)=FLOOR(TODAY()-2) AND (HOUR(TEMPO)=2 OR HOUR(TEMPO)=5)

    ORDER BY OID_METER,DATA,TEMPO DESC ;

12 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Eduardo,

the answers to your questions completely depend on your business analytics needs. If you have many transactions for the same Meter and Type, and you don't care about Date/Time, you can:

- concatenate all transactions into one table instead of joining, and that would eliminate duplication

- aggregate all transactions into one number per Meter and Type, using GROUP BY.

Regarding your second question - having 2 tables with 3 common fields would cause a Synthetic Key. Personally, I'm of the opinion that synthetic keys should be avoided. Other experts may tell you otherwise. Ultimately, the structure should be driven by your business needs.

cheers.

Oleg Troyansky

eduardo_dimperio
Specialist II
Specialist II
Author

Hey Oleg,

I need to show the consume of every meter by 3 consecutive days, between 2 and 5 am. For that reason is important date and hour. The consume is calculated by the diference between value ate 5 am - value at 2 am.

eduardo_dimperio
Specialist II
Specialist II
Author

Oleg, I tried your recommendation and now it works.

Thank you for your help !