Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
Once again i need some help and thank's for all help that community are give to me
Well, i dont understand why my code using Peek() is returning NULL.
TMP3 is running ok
TMP3:
LOAD
DISTINCT
OID_METER,
DATE_READ,
ID_LEITURA,
NAME_SYSTEM,
NAME_GROUP,
NEIGHB_SYSTEM,
CITY_SYSTEM,
NAME_TYPE_METER,
TYPE_CONSTANT,
NAME_UC,
ADDRESS_BLOCK_UC,
VALUE_READ,
If(OID_METER= Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),VALUE_READ) as CONSUMODIA0,
If(OID_METER= Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),VALUE_READ) as CONSUMODIA1,
If(OID_METER= Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),VALUE_READ) as CONSUMODIA2
RESIDENT TMP2
WHERE (HOUR(DATE_READ)=2 OR HOUR(DATE_READ)=5) AND MINUTE(DATE_READ)=0
ORDER BY OID_METER;
DROP TABLE TMP,TMP2;
ANALISE_TMP:
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,
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
RESIDENT TMP3
ORDER BY OID_METER DESC;
DROP TABLE TMP3;
I don't understant why PEEK(CONSUMODIA0)- CONSUMODIA0 return null if i have value on this fields
I did another modification and didnt work too
ANALISE_TMP:
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,
CONSUMODIA0,
CONSUMODIA1,
CONSUMODIA2,
CONSUMO_REAL0
If((OID_METER= Peek(OID_METER)) AND (DAY(DATE_READ) = 22),Peek(CONSUMODIA0))AS CONSUMO_REAL0
RESIDENT TMP3
ORDER BY OID_METER DESC;
DROP TABLE TMP3;
Try this:
ANALISE_TMP:
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,
CONSUMODIA0,
CONSUMODIA1,
CONSUMODIA2,
If (OID_METER = PEEK(OID_METER), CONSUMODIA0 - PEEK(CONSUMODIA0)) AS CONSUMO_REAL0,
If (OID_METER = PEEK(OID_METER), CONSUMODIA1 - PEEK(CONSUMODIA1)) AS CONSUMO_REAL1,
If (OID_METER = PEEK(OID_METER), CONSUMODIA2 - PEEK(CONSUMODIA2)) AS CONSUMO_REAL2
Resident TMP3
Where Len(Trim(CONSUMODIA0)) > 0
Order By OID_METER;
DROP TABLE TMP3;
I tried this once when I couldn't get Peek to work (or maybe it was in Power BI)
Add rowno() to the main table when ordering
Create a new table from this table loading RowNo() + 1 plus the required field (renamed) with a left join (by RowNo()
then do the calculation and see if the problem still exists
Does Peek() not need single quotes around the field, like Peek('OID_METER')
Hi everyone,
I solve the problem with this code below:
LOAD
DISTINCT
OID_METER,
DATE_READ,
ID_LEITURA,
NAME_SYSTEM,
NAME_GROUP,
NEIGHB_SYSTEM,
CITY_SYSTEM,
NAME_TYPE_METER,
TYPE_CONSTANT,
NAME_UC,
ADDRESS_BLOCK_UC,
VALUE_READ,
If(OID_METER= Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()) AND VALUE_READ-PEEK(VALUE_READ)>0,VALUE_READ-PEEK(VALUE_READ)) as CONSUMODIA0,
If(OID_METER= Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1)AND VALUE_READ-PEEK(VALUE_READ)>0,VALUE_READ-PEEK(VALUE_READ)) as CONSUMODIA1,
If(OID_METER= Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2)AND VALUE_READ-PEEK(VALUE_READ)>0,VALUE_READ-PEEK(VALUE_READ)) as CONSUMODIA2
RESIDENT TMP2
WHERE (HOUR(DATE_READ)=2 OR HOUR(DATE_READ)=5) AND MINUTE(DATE_READ)=0
ORDER BY OID_METER,DATE_READ;
DROP TABLE TMP,TMP2;
NoConcatenate
TMP4:
LOAD
DISTINCT
OID_METER,
DATE_READ,
ID_LEITURA,
NAME_SYSTEM,
NAME_GROUP,
NEIGHB_SYSTEM,
CITY_SYSTEM,
NAME_TYPE_METER,
TYPE_CONSTANT,
NAME_UC,
ADDRESS_BLOCK_UC,
VALUE_READ,
CONSUMODIA0,
CONSUMODIA1,
CONSUMODIA2
RESIDENT Exclusao
WHERE HOUR(DATE_READ)=5;
DROP TABLE Exclusao;
I still have one problem, but is not related with Peek(), so i'll open another ticket.
Thanks all