Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi people,
I need some help please.
I did this code below, with the result like the image, but i need to show Consumo0 (today), Consumo1(today -1) and Consumo2(today -2), side by side.
The problem is each read are was diferent day. How can i do this?
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,
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 WHERE OID_METER=70794 AND NAME_UC ='ZELADORIA'
ORDER BY OID_METER DESC;
Hi everyone,
I finally resolve the problem:
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
after i use SUM() in the grafic part.
Thank all for the help
Please elaborate what output do you want with at least two values of OID_METER.
What is your desired output here? You have 5 values for Consumodia0, 8 for Consumodia1, and 2 for Consumodia2.
Hi,
For output i need get in the same line all values that occur in the same hour but in diferent days.
Like value of Consumo0 in 11/12 at 10:00pm, Consumo1 in 12/12 at 10:00pm, Consumo2 in 13/12 at 10:00pm.
Well,
I did this and get the right output, but im not sure if is this best way to do.
ANALISE:
LOAD
ID_LEITURA,
NAME_SYSTEM,
NAME_GROUP,
NEIGHB_SYSTEM,
CITY_SYSTEM,
OID_METER,
NAME_TYPE_METER,
NAME_UC,
ADDRESS_BLOCK_UC,
CONSUMODIA0
RESIDENT ANALISE_TMP;
// WHERE CONSUMODIA0>=0.005 AND CONSUMODIA1>=0.005 AND CONSUMODIA2>=0.005;
left join(ANALISE)
LOAD
CONSUMODIA1
RESIDENT ANALISE_TMP;
left join(ANALISE)
LOAD
CONSUMODIA2
RESIDENT ANALISE_TMP;
If there is a dimension for the table that is unique then you can use an aggregation for the measure like avg(Consumodio0) that will summarize the value of Consumodia on that dimension, but right now none of the dimensions (columns on the left) are unique.
Hi Eduardo,
I'd recommend creating a master calendar, and tagging days as Today, [Today -1], [Today -2] then you will be able to use a dimension in a pivot table, as columns.
The advantage being that you would also be able to use other periods of time, like 'This Month', 'Last' Month' - it is a good technique to learn...
Check out Henric's post The Master Calendar
Alternatively, create a dimension using Date(Today()) - DATE_READ as [Days Offset]
Kind regards
Marty.
Brian, my system are getting more than one value by hour so i need the highest and I try this:
ANALISE:
LOAD
ID_LEITURA,
NAME_SYSTEM,
NAME_GROUP,
NEIGHB_SYSTEM,
CITY_SYSTEM,
OID_METER,
NAME_TYPE_METER,
NAME_UC,
ADDRESS_BLOCK_UC,
HORA,
MAX(CONSUMODIA0)
RESIDENT ANALISE_TMP;
but get this error
Ocorreram os seguintes erros:
Invalid expression
Thanks Martyn, ill study this better, but this project will have only 3 days range, always.
You need a comma after the max expression