Alighing Fields using Distinct

[eduardo_dimperio]

Hi,

I dont know why my distinct doesnt work.

I have from input 3 Dates of consuming energy, today, today-1 and today -2 and i need put this consuming data by hour, side by side. I try everithing even transform my Data in Date, get the hour of this date and select distinct hour withou sucess.

Below the code and the image of my output right now and a excel with what a want to be my output.

Help please

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,

    Date(DATE_READ) AS HORA,

    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, DATE_READ DESC;

DROP TABLE TMP,TMP2;

ANALISE:

LOAD

    DISTINCT HOUR(HORA) AS HORA0,

  ID_LEITURA,

    NAME_SYSTEM,

    NAME_GROUP,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    OID_METER,

    NAME_TYPE_METER,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    CONSUMODIA0,

    CONSUMODIA1,

    CONSUMODIA2

    RESIDENT ANALISE_TMP;

[Gysbert_Wassenaar]

Replace the CONS* dimensions with measures: Sum(CONSUMODIA0), Sum(CONSUMODIA1) and Sum(CONSUMODIA2)

[sunny_talwar]

Seems like you are using CONSUMODIA0 as a dimension. Instead use Sum(CONSUMODIA0) as an expression and I think it should be fine

[eduardo_dimperio]

Hi, thank you, but this solve about 80% of my problem.

I said that because when i have value null like in CONSUMODIA0 and CONSUMODIA1, function SUM assume null as zero and that its a problem.

In my field, null means no registration, so i could have a consume but this value doesnt appear for some problem(hardware problem), but when a have zero that means no consume at all.

Can i,some how, show null instead zero ?

[sunny_talwar]

May be this:

If(Len(Trim(Avg(CONSUMODIA0))) > 0, Sum(CONSUMODIA0))

Or

If(Sum(CONSUMODIA0) > 0, Sum(CONSUMODIA0))

[eduardo_dimperio]

Hey Sunny !

That soluction works fine, but one last question please.

If i dont want show a whole line if i get some zero value, like this:

CONSUMODIA0  CONSUMODIA1  CONSUMODIA2

           0                          10                    3

I need not to show this line, how can i do it?

Thank you

[sunny_talwar]

May be check all three expressions for 0 before giving it any value

If(Exp1 <> 0 and Exp2 <> 0 and Exp3 <> 0, Exp1)

If(Exp1 <> 0 and Exp2 <> 0 and Exp3 <> 0, Exp2)

If(Exp1 <> 0 and Exp2 <> 0 and Exp3 <> 0, Exp3)

[eduardo_dimperio]

I Try this:

ANALISE_FINAL:

LOAD

  HORA0,

    OID_METER,

    Sum(CONSUMODIA0),

    Sum(CONSUMODIA1),

    Sum(CONSUMODIA2)

RESIDENT ANALISE WHERE Sum(CONSUMODIA0) >0 AND Sum(CONSUMODIA1)>0 AND Sum(CONSUMODIA2)>0

GROUP BY OID_METER,HORA0

ORDER BY HORA0;

but get this error:

Ocorreram os seguintes erros:

Unknown Error

O erro ocorreu aqui:

ANALISE_FINAL: LOAD HORA0, OID_METER, Sum(CONSUMODIA0), Sum(CONSUMODIA1), Sum(CONSUMODIA2) RESIDENT ANALISE WHERE Sum(CONSUMODIA0) >0 AND Sum(CONSUMODIA1)>0 AND Sum(CONSUMODIA2)>0 GROUP BY OID_METER,HORA0 ORDER BY HORA0

[sunny_talwar · Accepted solution]

Try this:

ANALISE_FINAL:

LOAD *

WHERE NEW_CONSUMODIA0 > 0 AND NEW_CONSUMODIA1 > 0 AND NEW_CONSUMODIA2 >0;

LOAD HORA0,

    OID_METER,

    Sum(CONSUMODIA0) as NEW_CONSUMODIA0,

    Sum(CONSUMODIA1) as NEW_CONSUMODIA1,

    Sum(CONSUMODIA2) as NEW_CONSUMODIA2

RESIDENT ANALISE

GROUP BY OID_METER, HORA0;

[eduardo_dimperio]

Its alive !!!

This preceding load trick, very nice solution. I still dont get very well how to use or how the compiler knows the right place where that 'where' goes.

But anyway thank you, its work very well !