topic Multiply two fields on script in QlikView

Multiply two fields on script

Thu, 15 Oct 2015 17:36:49 GMT

Hi all,

I want to multiply fields on script but I need help.

This is what I have:

País	ID_Dato	TIPO_TIP	% POR TIPO
Alemania	COR.001	DEF	90,0000%
Alemania	COR.001	KEY	95,0000%
Spain	COR.002	DEF	99,0000%
Spain	COR.002	KEY	98,0000%
Spain	COR.002	DOC	40,0000%

I want to have the result at ID_Dato level, like this:

País	ID_Dato	% DATO
Alemania	COR.001	85,5000%
Spain	COR.002	38,8080%

I have to multiply them (COR.001 = 85,500% = 90,00%*95,00% and COR.002 = 38,808% = 99,00%*98,00%*40,00%).

I tried SUM, ONLY, AVG.. and I didn't get anything.

Thank you.

Re: Multiply two fields on script

crystles — Thu, 15 Oct 2015 18:02:16 GMT

Which fields are you trying to multiply?

You would have to make a distinction between the two COR fields, by the TIPO_TIP field I am assuming. Maybe that is why it isn't working?

Could you post your script code?

Re: Multiply two fields on script

raajaswin — Thu, 15 Oct 2015 18:04:46 GMT

Try AGGR function to group things.

Hope it will work

Re: Multiply two fields on script

sunny_talwar — Thu, 15 Oct 2015 18:09:05 GMT

This?

Script:

Table:

LOAD País,

ID_Dato,

TIPO_TIP,

SubField([% POR TIPO], ',', 1)/100 as [% POR TIPO]

FROM

[https://community.qlik.com/thread/187022]

(html, codepage is 1252, embedded labels, table is @1);

Join(Table)

LOAD País,

ID_Dato,

Evaluate(Concat([% POR TIPO], '*')) as NewField

Resident Table

Group By País, ID_Dato;

Re: Multiply two fields on script

raajaswin — Thu, 15 Oct 2015 18:15:51 GMT

=aggr((Exp(Sum(Log(%POR TIPO)))),ID_Dato)

Re: Multiply two fields on script

maxgro — Thu, 15 Oct 2015 18:17:41 GMT

SET ThousandSep=".";

SET DecimalSep=",";

LOAD rowno() as id, País, ID_Dato, TIPO_TIP,

((replace([% POR TIPO], '%', ''))+0)/100 as Field

FROM

[https://community.qlik.com/thread/187022]

(html, codepage is 1252, embedded labels, table is @1);

NoConcatenate

load id, País, ID_Dato, TIPO_TIP,

if(País=Peek(País) and ID_Dato=peek(ID_Dato), peek(Result)*Field, Field) as Result

Resident Z

order by País, ID_Dato, id;

DROP Table Z;

NoConcatenate

load * Resident Y

Where not (País=Peek(País) and ID_Dato=peek(ID_Dato))

Order by id desc;

DROP Table Y;

Re: Multiply two fields on script

sinanozdemir — Thu, 15 Oct 2015 20:26:51 GMT

Way easier answer by using some Math functions:

And here is the data model:

Use of Log(), Exp() and Sum() functions.

Hope this helps.

Re: Multiply two fields on script

Fri, 16 Oct 2015 07:37:55 GMT

You saved my life!

Re: Multiply two fields on script

sinanozdemir — Fri, 16 Oct 2015 13:11:50 GMT

You are welcome

One of my coworkers is a Math genius.