"Concatenate" Fields

[eduardo_dimperio]

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;

[shubham_singh]

Please elaborate what output do you want with at least two values of OID_METER.

[Anonymous]

What is your desired output here?  You have 5 values for Consumodia0, 8 for Consumodia1, and 2 for Consumodia2.

[eduardo_dimperio]

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.

[eduardo_dimperio]

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;

[Anonymous]

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.

[martynlloyd]

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.

[eduardo_dimperio]

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

[eduardo_dimperio]

Thanks Martyn, ill study this better, but this project will have only 3 days range, always.

[Anonymous]

You need a comma after the max expression