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
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
you condition for evaluation could be failing that's why
TRY as below with Previous
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 =PREVIOUS(OID_METER), PEEK(CONSUMODIA0)- CONSUMODIA0) AS CONSUMO_REAL0,
If (OID_METER =PREVIOUS(OID_METER), PEEK(CONSUMODIA1)- CONSUMODIA1) AS CONSUMO_REAL1,
If (OID_METER =PREVIOUS(OID_METER), PEEK(CONSUMODIA2)- CONSUMODIA2) AS CONSUMO_REAL2
RESIDENT TMP3
ORDER BY OID_METER DESC;
DROP TABLE TMP3;
Try this:
If(OID_METER = Peek(OID_METER), RangeSum(Peek(CONSUMODIA0), -CONSUMODIA0) as CONSUMO_REAL0,
Hi,
The output for the code "Peek(OID_METER)" is null which is stored in CONSUMODIA0 column of TMP3. That is being used in the "ANALISE_TMP".
Peek() function has two uses
1)Peek(columnname): Returns previous row value of the column. if no previous row value i.e incase of the first record where there is no record before the 1st record, it will return null.We need to handle it.
Example below
_MASTER_KEY:
load * Inline [
monther
1
2
3
4
5
];
MASTER_KEY:
load monther,
if(isnull(peek(monther)),0,(peek(monther))) as previous_value
Resident _MASTER_KEY;
OUTPUT is below
monther , previous_value
1, 0
2, 1
3, 2
4, 3
5, 4
2)Peek('Columnname',recordnum,'Tablename') :
in this case it will get the desired record value(given from recordnum) of the columnname given through "Columnname" f the given table given from "Tablename".Which should be previously loaded table.Check belowexample.
Peek('monther',3,'_MASTER_KEY');
returns 4
important things to note 1) Peek uses 0 as first record
2) single quotes are required to name a column and table.
In your case, u are using the first peek() so, please handle the null as stated in the example1
Hi Synny, doesnt work
Vineeth, doesnt work bro.
When you say doesn't work, does it mean that you still see null or are you running into other issues?
I try this, but not work
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) AND Isnull(Peek(CONSUMODIA0)=0 ),(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;
I tried filter by Date, because CONSUMODIA0 only have record when date=today(). So i have value 22/12 5:00 and 22/12 2:00.
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) AND DATE_READ = TODAY(),Peek(CONSUMODIA0)AS CONSUMO_REAL0
RESIDENT TMP3
ORDER BY OID_METER DESC;
and didnt work too
Other issues, get wrong numbers output:
The right output need to be a single row with 20 as value (177959 - 177939)