topic Re: Aggr function in QlikView

Aggr function

Wed, 04 Jun 2014 15:09:59 GMT

Hi to all,

I tried everything but I can't resolve this issue,hope someone can help me

I have this pivot table.

I'll try to explain what I want to do:

If I have Dato = 'HC PROCESS FLARING' in a SITE, when DATO = 'HC PROCESS FLARING' only Column "hc flaring sm3" must be populated; when DATO = 'PROCESS FLARING' only column Total Flaring sm3, as shown in Figure.
If site doesn't have DATO = 'HC PROCESS FLARING' in a SITE I need the same value for both columns.

I hope to do this in front end.

I think something with concat and aggrs, but I can't resolve it...any suggestions?

Thanks in advice!

Re: Aggr function

alexandros17 — Wed, 04 Jun 2014 15:24:51 GMT

Allega il documento e cercherò di darti una mano ....

Re: Aggr function

Anonymous — Wed, 04 Jun 2014 15:40:56 GMT

Hi Antonio,

Can you show me your QVW file ?

Hugs

Re: Aggr function

Wed, 04 Jun 2014 15:47:43 GMT

Hi guys, I know it's better but qvw it's 165 Mb, and it's classified

I hope to solve it in pivot table expressions and not in back end, if it's possible, because script it's terrible and huge(not mine, thanks god ).

Thanks for your help!

Antonio

Re: Aggr function

Anonymous — Wed, 04 Jun 2014 20:59:04 GMT

A little hint, don't have time at this moment for details...

This expression should return 0 for a SITE that doesn't have 'HC PROCESS FLARING' and >0 for anyhting else:

=index(concat(total<SITE> DATO, '|'), '|HC PROCESS FLARING|')

Re: Aggr function

Anonymous — Wed, 04 Jun 2014 21:19:12 GMT

Hello!

Try these two expressions.

Total Flaring [Sm3]: if(DATO <> 'HC PROCESS FLARING', Sum(Value))

HC Flaring [Sm3]: if(DATO <> 'PROCESS FLARING', Sum(Value))

Of course, substitute "Sum(Value)" with what you are trying to calculate. If they do not work, please post the dimensions and expressions you are using on that table.

Regards.

Re: Aggr function

Wed, 04 Jun 2014 21:23:50 GMT

Hi Michael, thanks for your trick, i'll try it tomorrow morning and let you know!

Thanks again!!

Re: Aggr function

Wed, 04 Jun 2014 21:27:22 GMT

Hi Bruno, thanks for your reply.

I'm using these two expressions, but this doesn't works in the second half of the pic (green).

Dimensions are Consociata, Site, Gruppo and Dato and two expression are like yours.

Thanks again!

Re: Aggr function

Anonymous — Wed, 04 Jun 2014 21:50:43 GMT

Hi, I had not understood very well what were your requirements. I think I know now. Using Michael's tip, let's try this:

Total Flaring [Sm3]: If(Index(Concat(Total<SITE> DATO, '|'), 'HC PROCESS FLARING') > 0, Sum(if(DATO <> 'HC PROCESS FLARING', Value)), Sum(Value))

HC Flaring [Sm3]: If(Index(Concat(Total<SITE> DATO, '|'), 'HC PROCESS FLARING') > 0, Sum(if(DATO <> 'PROCESS FLARING', Value)), Sum(Value))

If it doesn't work, we'll need more information about your data model.

Re: Aggr function

Thu, 05 Jun 2014 07:21:58 GMT

Hi Bruno,

doesn't work..

The correct behavior is Site "F", in the others Process Flaring should have both columns with the same value.

Re: Aggr function

Anonymous — Thu, 05 Jun 2014 12:06:45 GMT

That's not the behavior in the yellow part of you first image. Anyway, all you need is to change the second expression to

HC Flaring [Sm3]: If(Index(Concat(Total<SITE> DATO, '|'), 'HC PROCESS FLARING') > 0, Sum(Value), 0)

Re: Aggr function

Thu, 05 Jun 2014 12:19:45 GMT

Hi Bruno, thanks again.

Yellow part and green are different (one with HC process Flaring, other no).

Changing the second expression I have both column with the same values in every case, also in Site "F" and it's wrong.

Thanks again for your help!

Antonio