topic Re: Make data multiplications in QV? in QlikView

Make data multiplications in QV?

Wed, 14 Jan 2015 09:36:01 GMT

Hello,

I wanted to know if it was possible to make data multiplications in QV and thus generate new data, and how?

Let me explain, I uploaded several tables of data in QV:

-a table "operations" in which I listed all my marketing operations, with a target size (how many people I want for each operation), occurrence (how many times a year) and a budget per operation.

-in another table I have coefficients "of importance / weight 'per operation

I would like to calculate:

- The total number of targets: namely the target size x the occurrence

- The weight of each ope: ie the total number of targets x weight coef

These calculations are simple to do in excel but I would like to know if it is possible to generate directly via QV rather than importing ready-made results?

Thank you in advance for your reply, hoping that someone can help me

Re: Make data multiplications in QV?

ashfaq_haseeb — Wed, 14 Jan 2015 09:41:20 GMT

Hi,

Can you come up with some sample data and expected o/p.

Regards

ASHFAQ

Re: Make data multiplications in QV?

Wed, 14 Jan 2015 09:57:30 GMT

Hi, yeah so for example :

Table operations :

OP001	1	Banner	Banner xzy	Product1	17000	5	10000
OP002	2	Website	websitexxx	Product1	17000	1	10000
OP003	3	App	appmob	Product1	17000	2	10000

17 000 is target / 1 is occurrence / 10000 is budget

Table coeff :

Operation	Coeff
Website	0,05
Mobile app	0,06
Bannières internet promo/ non promo	0,02

I want to calculate :

Target total, ie :

banner : 17000 x 5 = 85000

website : 17000 x 1 = 17000

app : 17000 x 2 = 4000

and coef :

85000 x 0,05 = 4250

17000 x 0,06 = 1020

34000 x 0,02 = 680

I know it's simple calculation but would be nice to have it done into QV.

Any idea?

Re: Make data multiplications in QV?

tresB — Wed, 14 Jan 2015 10:16:08 GMT

Check the attached qvw. It's all about linking the tables properly in qv.

Re: Make data multiplications in QV?

ashfaq_haseeb — Wed, 14 Jan 2015 10:19:32 GMT

Hi,

Yes possible all that you need to have is proper link between the two.

tresesco gave an example of the same.

Regards

ASHFAQ

Re: Make data multiplications in QV?

Wed, 14 Jan 2015 10:26:33 GMT

Hi,

You could left join your Operations table with the coefficient data from Coeff table, so that on each line you have your coeff. After that drop the Coeff table and make a new resident Load in which you add a new column (target * occurrence * coeff) as total_taget. In this way you have the data already calculated in the source, although the reload will take a little bit more time, the data will be precalculated.

Re: Make data multiplications in QV?

its_anandrjs — Wed, 14 Jan 2015 10:49:29 GMT

Hi,

You can simple Left join Operation Master table to operation transaction table and do the calculation in any resident table and also left join it with based on Operation field to the transaction table.

Data:

Load * Inline [

Operation_Code, OpID, Operation,Op_Name, Product, "Product Size", Occurance

OP001, 1, Banner, Banner xzy, Product1, 17000, 5

OP002, 2, Website, websitexxx, Product1, 17000, 1

OP003, 3, App, appmob, Product1, 17000, 2

];

Left Join

Load * Inline [

Operation, Coeff

Banner, 0.05

Website, 0.06

App, 0.02

];

Left Join

Data:

LOAD

Operation,

Occurance*[Product Size] as [Target total],

Occurance*[Product Size]*Coeff as Coef

Resident Tmp;

Regards

Anand

Re: Make data multiplications in QV?

marchoctober — Wed, 14 Jan 2015 14:38:26 GMT

Hi Aurelie,

Is is really possible and task also is simple. Answers is above.

What I am asking now, is what kind of task in overall are you working on?

In order that my company is specializing in Stats, Gallups, Weights, Forecasts in QV, and we already made mindthreatened QV apps for customers in that filed.

We could help you directly in modelling and user experience, etc. Contact me if you need help.

Many thanks.

Re: Make data multiplications in QV?

Wed, 14 Jan 2015 16:11:20 GMT

Thank you to all of you for your help which is very appreciated

Re: Make data multiplications in QV?

its_anandrjs — Wed, 14 Jan 2015 17:18:33 GMT

You are Welcome.

Regards,

Anand