Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ;
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
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.
Oleg, I tried your recommendation and now it works.
Thank you for your help !