topic Re: Multiply Table content by Cofficient in QlikView

Multiply Table content by Cofficient

Wed, 11 Dec 2013 13:50:55 GMT

Good moring,

I ask for your advice because I want to do calculating in a script.

I really don't know how to write relation.

Table [Investissement] Table [ki]

A k1

B k2

C k3

.. ..

N kn

To obtain Sum (A*k1 + B*k2 + C*k3.... N*kn)

And realised the Sum of all multiply

Do y use loop ? and Incrément ?

Thanks for your reply

Alphonse

Re: Multiply Table content by Cofficient

Anonymous — Wed, 11 Dec 2013 13:57:54 GMT

Alphonse

Is there a common field between your 2 tables that can be used to link them ?

[Investissement]
[ki]

Best Regards, Bill

Re: Multiply Table content by Cofficient

Wed, 11 Dec 2013 14:39:09 GMT

Hello,

Tkanks for your reply.

No, there are 2 tables independants

Re: Multiply Table content by Cofficient

Anonymous — Wed, 11 Dec 2013 15:52:55 GMT

Alphonse

How would one know which row value from [Investissement] to multiply with which row value from [ki] ?

And what table with what fields would you want this calculated value placed in ?

I am quite confused here, so it would be great if you could share a sample qvw, with some sample data via an Inline load, plus a description of the output table field you are after ?

Best Regards, Bill

Re: Multiply Table content by Cofficient

Wed, 11 Dec 2013 16:28:42 GMT

Hello,

If I understand, maybe you can try something like this

Re: Multiply Table content by Cofficient

Wed, 11 Dec 2013 16:42:43 GMT

Good morning,

Sorry, I can't read Test.qvw, because I am testing this produit.

In fact I try to calculate IRR on a projet. I have 2 tables : Resultat and Coefficient as this picture.

Actually I do this on Excel file and after reload the result. But I would like to try multiply this 2 tables in another table result which is empty at the begining. And if possible to make SUM of all figures.

But I am beginner on QV...

Tkanks in advance

Alphonse

Re: Multiply Table content by Cofficient

Wed, 11 Dec 2013 16:44:18 GMT

Hi,

I forgot to join the pic on tables

Re: Multiply Table content by Cofficient

Anonymous — Wed, 11 Dec 2013 16:50:24 GMT

Alphonse

If your source data is in Excel file ~~could you share them with us ?~~

Best Regards, Bill

Re: Re: Multiply Table content by Cofficient

Wed, 11 Dec 2013 17:00:51 GMT

Hello Bill,

Yes

I have 2 tables : Resultat and Coefficient

I would like in QV multiply each ligne and obtain calculation and a Resulte table. And If possible make the Sum of all data in Result.

Thanks in advance.

Alphonse

Re: Multiply Table content by Cofficient

Wed, 11 Dec 2013 17:09:11 GMT

Hello Alphonse,

The script in Test.qvw is the following:

TABLE_1_TMP:

LOAD * INLINE [

FirstColumnT1

];

TABLE_2_TMP:

LOAD * INLINE [

FirstColumnT2

5.5

3.2

1.9

4.3

8.0

7.5

2.2

9.1

0.8

];

TABLE1:

LOAD FirstColumnT1 as Investissement,

RowNo() AS INDICE

Resident TABLE_1_TMP;

DROP Table TABLE_1_TMP;

Join(TABLE1)

TABLE2:

LOAD FirstColumnT2 as ki,

RowNo() as INDICE

Resident TABLE_2_TMP;

DROP Table TABLE_2_TMP;

TABLE3: // RESULT

LOAD *,

Investissement*ki as Result

Resident TABLE1;

DROP Table TABLE1;

Best regards.

Re: Re: Re: Multiply Table content by Cofficient

Anonymous — Wed, 11 Dec 2013 17:11:32 GMT

Alphonse

One thing at time....

Try this in your load script to load your xls and advise if it produces the data table in QlikView you are after ?

Data:

LOAD Annee,

Investissement,

Recette,

Resultat,

[Valeur du coefficient],

( Resultat * [Valeur du coefficient] ) as [Derived Value] ,

FROM

Sample_data.xls

(biff, embedded labels, table is Engagement$);

Best Regards, Bill