Multiply two fields on script

[#1]

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.

[crystles]

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?

[raajaswin]

Try AGGR function to group things.

Hope it will work

[sunny_talwar]

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;

[raajaswin]

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

[maxgro]

SET ThousandSep=".";

SET DecimalSep=",";

Z:

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);

Y:

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;

T:

NoConcatenate

load * Resident Y

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

Order by id desc;

DROP Table Y;

[sinanozdemir · Accepted solution]

Way easier answer by using some Math functions:

And here is the data model:

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

Hope this helps.

[#8]

You saved my life!

[sinanozdemir]

You are welcome

One of my coworkers is a Math genius.