Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
eduardo_dimperio
Specialist II
Specialist II

"Concatenate" Fields

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;

CONSUME.PNG

1 Solution

Accepted Solutions
eduardo_dimperio
Specialist II
Specialist II
Author

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

View solution in original post

15 Replies
shubham_singh
Partner
Partner

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

brianrmacdonald
Creator
Creator

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

eduardo_dimperio
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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;

CONSUME.PNG

brianrmacdonald
Creator
Creator

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
Partner
Partner

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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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

brianrmacdonald
Creator
Creator

You need a comma after the max expression