topic Re: Suma con función Fabs desde Script in QlikView

Suma con función Fabs desde Script

Tue, 08 Jul 2014 08:42:38 GMT

Buenos Días,

Tengo la siguiente tabla residente:

Clave	Columna1	Columna2
JHabr2013	-37556,01	32097,15
JHago2013	-28714,98	33537,34
JHdic+2013	-23793,06	-1065,53
JHdic2013	-49990,41	33133,13
JHene2013	-57532,14	33679,99
JHfeb2013	-36602,73	33634,57
JHjul2013	-34940,28	32792,56
JHjun2013	-33227,27	32562,92
JHmar2013	-7065,01	32693,15
JHmay2013	-32576,32	31875,36
JHnov2013	-31461,78	34579,34
JHoct2013	-35936,24	32662,81
JHsep2013	-32237,95	32566,89
	-441634,18	394749,68

Quiero obtener, desde Script, un nuevo campo, que multiplique la suma de las dos columnas, pero en valor absoluto.

Es decir 441.634,18 * 394.749,68 = 174.334.951.232,0624

Necesito calcular la suma de las columnas, teniendo en cuenta los negativos, pero a la hora de multiplicar los valores resultantes de esas sumas, que lo haga sobre valor absoluto.

Si hago fabs(sum(Columna1) * fabs(sum(Columna2), pierdo los negativos y el total no es el correcto.

Alguien me puede orientar?

Gracias!

Re: Suma con función Fabs desde Script

alexandros17 — Tue, 08 Jul 2014 08:44:59 GMT

In the script you can do:

Load

...

Fabs(Columna1 * Columna2) as newCol

...

In the table you can do (in an expression):

Fabs(Sum(Columna1 * Columna2))

Let me know

Re: Suma con función Fabs desde Script

Tue, 08 Jul 2014 09:52:25 GMT

Hi Alesandro,

Thanks for your answer...

In that case, the sum of new column is 13.827.668.351 and the result that I need is:

Sum Column1 * Sum Column2

441.634,18 * 394.749,68 = 174.334.951.232,0624

Clave	Columna1	Columna2	NewColumn
JHabr2013	-37556,01	32097,15	1205440886
JHago2013	-28714,98	33537,34	963024047,4
JHdic+2013	-23793,06	-1065,53	25352219,22
JHdic2013	-49990,41	33133,13	1656338753
JHene2013	-57532,14	33679,99	1937681900
JHfeb2013	-36602,73	33634,57	1231117084
JHjul2013	-34940,28	32792,56	1145781228
JHjun2013	-33227,27	32562,92	1081976935
JHmar2013	-7065,01	32693,15	230977431,7
JHmay2013	-32576,32	31875,36	1038381927
JHnov2013	-31461,78	34579,34	1087927588
JHoct2013	-35936,24	32662,81	1173778579
JHsep2013	-32237,95	32566,89	1049889771

Re: Suma con función Fabs desde Script

alexandros17 — Tue, 08 Jul 2014 10:25:23 GMT

Could you please verify this expression?

Sum(TOTAL fabs(Columna1)) * Sum(TOTAL fabs(Columna2))

Re: Suma con función Fabs desde Script

Tue, 08 Jul 2014 10:57:26 GMT

With this expression, in set analisys, the results is near that I need, but not exactly...

But this expression is not allowed in Script Code.

Probably i'm wrong in my approach.

Re: Suma con función Fabs desde Script

alexandros17 — Tue, 08 Jul 2014 11:51:08 GMT

This expression must be used in the UI object, not in the script ...

Re: Suma con función Fabs desde Script

alexandros17 — Tue, 08 Jul 2014 11:56:12 GMT

this is what you need as your expression ...

Fabs(Sum(TOTAL Columna1) * Sum(TOTAL Columna2))

Re: Suma con función Fabs desde Script

Tue, 08 Jul 2014 12:05:09 GMT

Hi Alessandro,

Exactly, this expression, return the value, but muy objective, was obtain it in the script.

I will continue to try.

Thanks for your time.

Re: Suma con función Fabs desde Script

alexandros17 — Tue, 08 Jul 2014 12:08:38 GMT

Here it is the script:

Orig:

LOAD * Inline [

Clave, Columna1, Columna2

JHabr2013, '-37556,01', '32097,15'

JHago2013, '-28714,98', '33537,34'

JHdic2013, '-23793,06', '-1065,53'

JHdic2013, '-49990,41', '33133,13'

JHene2013, '-57532,14', '33679,99'

JHfeb2013, '-36602,73', '33634,57'

JHjul2013, '-34940,28', '32792,56'

JHjun2013, '-33227,27', '32562,92'

JHmar2013, '-7065,01', '32693,15'

JHmay2013, '-32576,32', '31875,36'

JHnov2013, '-31461,78', '34579,34'

JHoct2013, '-35936,24', '32662,81'

JHsep2013, '-32237,95', '32566,89'

];

NewTab:

NoConcatenate

LOAD Fabs(Sum(Columna1) * Sum(Columna2)) as totale Resident Orig;

Re: Suma con función Fabs desde Script

Rosario_Aguilar — Thu, 03 Sep 2020 18:56:52 GMT

Hi Alexandros,

I try to do this last way, but this not worked. I need to do calculate EBITDA, and I used this way, because i need this solution type in the scripts, but i can't . Can you tell me if you have another solution?