topic Re: Mapping in/between the fields in order to do the calculations in QlikView

Mapping in/between the fields in order to do the calculations

Wed, 25 Nov 2015 16:36:22 GMT

Hi all,

I need to create a mapping like this.

plant name	print center1	print line1	print center2	print line2
D1	33CUX345	P-2C	32CUX345	PCO-2C
D1	33CUX363	P-4C	32CUX363	PCO-4C
F1	33LAFP02	P-6C	32LAFP02	PCO-6C
I1	33INCB79	P-6CFOV	32INCB79	PCO-6CFOV

For each plant, by using this mapping, I should calculate the volume of print line1 divided by the volume of print line2. The calculation should also be available at print center niveau.

All the "print center1 and print center2" (e.g. 33CUX345) are the different elements in the same field"print center".

and all the "print line1 and print line2" are the elements of the same field "print line".

what i wrote down in the script :

Print:

LOAD * INLINE [

_plant, _print_c1, _print_lin1, _print_c2, _print_lin2,

D1, 33CUX345, P-2C, 32CUX345, PCO-2C

...

]

The current expression is :

sum({<Version='Actual', Year='2015', Department_code={3}, _Print_lin1={*}, _Print_c1={*}>}Volume)/sum({<Version='Actual', Year='2015', Department_code={9}, _Print_lin2={*}, _Print_c2={*}>}Volume)

To separate the print centre1 and 2, i use their "department_code" ("3" is print center1 and "9" is center2).

For now the calculations don't work...

Does anybody know where the problem is? in the script or in the expression or in both of them?

Many thanks.

Ce message a été modifié par : Q H

Re: Mapping in/between the fields in order to do the calculations

ramoncova06 — Wed, 25 Nov 2015 17:10:00 GMT

your set analysis seems to be the problem

you need to add curly brackets to every set

sum({<Version={'Actual'}, Year={'2015'}, Department_code={3}, _Print_lin1={*}, _Print_c1={*}>}Volume)/sum({<Version={'Actual'}, Year={'2015'}, Department_code={9}, _Print_lin2={*}, _Print_c2={*}>}Volume)

Re: Mapping in/between the fields in order to do the calculations

Wed, 25 Nov 2015 17:24:31 GMT

Tks for the comments!

I add them now but still don't work

Re: Mapping in/between the fields in order to do the calculations

t_chetirbok — Wed, 25 Nov 2015 17:35:08 GMT

Hi!

Try this

sum({<Version={'Actual'}, Year={2015}, Department_code={3}>}Volume)/sum({<Version={'Actual'}, Year={2015}, Department_code={9}>}Volume).

Or show us some example qvw

Re: Mapping in/between the fields in order to do the calculations

Thu, 26 Nov 2015 17:39:16 GMT

Hi,

thanks but don't work neither

Re: Mapping in/between the fields in order to do the calculations

ramoncova06 — Mon, 30 Nov 2015 15:38:52 GMT

ok, put that same expression inside of a text box, does it say if you have an error in it ?

Re: Mapping in/between the fields in order to do the calculations

Wed, 02 Dec 2015 17:49:05 GMT

hello,

Hi, I've attached a simpe qvw and 3 excel files : " Test print.xls" is the database and "result.xls" the table i what to show in the report. In the qvw you could see the calculations are done per plant instead of per line. Thanks to let me know if it's not clear.

Re: Mapping in/between the fields in order to do the calculations

Wed, 02 Dec 2015 17:50:51 GMT

Hi,

i've attached 1 simple qvw and 3 excel files.

Thanks to let me know if it's not clear.

Re: Mapping in/between the fields in order to do the calculations

t_chetirbok — Thu, 03 Dec 2015 07:13:48 GMT

Hello!

try this expressions: 1) sum(if(gp_pr=gp,quantity)) 2) sum(if(gp_co=gp,quantity))