Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

robert2012
New Contributor III

Compare month to year with two different tables

Hi, I have the problem, that I would like to calculate something per month compared to an value per year. In Table 1 there are the months and in Table 2 there are the year.

Table 1:

A_Jan;                10

A_Feb;               13

A_Mär;               13

A_Apr;                15

B_Jan;                 10

B_Feb;                15

B_Mär;                14

..

Table 2:

A;                          3

B;                          2

C;                          4

..

I want to calculate in this way:


A_Jan / A;  

A_Feb / A

A_Mär / A

...;  

B_Jan / B;  

B_Mär / B

...

How could I handle this? Any Idea? Thank you for help!

1 Solution

Accepted Solutions
manideep78
Valued Contributor

Re: Compare month to year with two different tables

Please find this.

7 Replies

Re: Compare month to year with two different tables

SET

ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';



Table_1:

loadinline

[

month,valMonth

A_Jan,                10

A_Feb,               13

A_Mar,               13

A_Apr,                15

B_Jan,                 10

B_Feb,                15

B_Mar,                14

]
;



Table_2:

LOAD * Inline

[

year,valYear

A,                          3

B,                          2

C,                         4

]
;





T1:

NoConcatenate

LOAD

*,

left(month,1) as year

Resident Table_1;     



Left Join



Table_2:

LOAD * Inline

[

year,valYear

A,                          3

B,                          2

C,                         4

]
;

Hope it helps

robert2012
New Contributor III

Re: Compare month to year with two different tables

Thank you for your fast answer, but I think you got me wrong. Think, I explained bad - sorry.

My problem is, that I don't know how to divide the diffrenet values as there is a different number of lines in the tables:

eg.

Table 1            Table 2   = Result

A_Jan (10)     :   A (3) = 3,33

A_Feb  (13)    :    A (3) = 4,33

A_... (xy)        :     A (y) = x

B_Jan            :      B =

B_Feb            :      B =

and so on.. I have a lot of different types.

Hope it is esasier now!?

Re: Compare month to year with two different tables

Well, but I think that you are a step before the solution because you have now a table where in each row you have values

year     month     valyear     valmonth

so you just have to compute valmonth / valyear for each row and then sum or aggregate or count or ... the result ... am I wrong?

Hope it helps

robert2012
New Contributor III

Re: Compare month to year with two different tables

Ok, now I understand ...

How will I get the result in the second step? How can I tell QV to compare the right rows.

Thank you for your patience.

Re: Compare month to year with two different tables

tell me what kind of graphical representation do you want and I'll try to help, let me know fields (columns) you want.

manideep78
Valued Contributor

Re: Compare month to year with two different tables

Please find this.

robert2012
New Contributor III

Re: Compare month to year with two different tables

Perfect, thank you very much!!!

Community Browser