Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Way easier answer by using some Math functions:
And here is the data model:
Use of Log(), Exp() and Sum() functions.
Hope this helps.
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?
Try AGGR function to group things.
Hope it will work
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;
=aggr((Exp(Sum(Log(%POR TIPO)))),ID_Dato)
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;
Way easier answer by using some Math functions:
And here is the data model:
Use of Log(), Exp() and Sum() functions.
Hope this helps.
You saved my life!
You are welcome
One of my coworkers is a Math genius.