Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Valued Contributor II

Alighing Fields using Distinct

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;

 

CONSUME.PNGOUTPUT.PNG

1 Solution

Accepted Solutions
Highlighted

Re: Alighing Fields using Distinct

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;

View solution in original post

9 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Alighing Fields using Distinct

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


talk is cheap, supply exceeds demand
Highlighted

Re: Alighing Fields using Distinct

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

Highlighted
Valued Contributor II

Re: Alighing Fields using Distinct

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 ?

Highlighted

Re: Alighing Fields using Distinct

May be this:

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

Or

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

Highlighted
Valued Contributor II

Re: Alighing Fields using Distinct

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

Highlighted

Re: Alighing Fields using Distinct

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)

Highlighted
Valued Contributor II

Re: Alighing Fields using Distinct

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

Highlighted

Re: Alighing Fields using Distinct

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;

View solution in original post

Highlighted
Valued Contributor II

Re: Alighing Fields using Distinct

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 !