Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
anuragsapanbhar
Contributor

Append Calculated Data To An Existing Table

Hi,

Could anyone help me with the following problem please.

I have 2 tables. One is data table and another one is Exchange rate. The data table has 3 rows, but I would want to append 2 more rows to the existing one (in yellow)

  1. Row C as a Percentage of Row A
  2. Row B is in Local Currency so Another Row in USD (B in USD).

untitled.png

Kindly help me to resolve the issue. Thanks in advance for your assistance. -Regards-

Tags (1)
1 Solution

Accepted Solutions

Re: Append Calculated Data To An Existing Table

Here you go

Table:

LOAD Dim,

Month(Date#(Month, 'MMM')) as Month,

Value;

LOAD * INLINE [

    Dim, Month, Value

    A, JAN, 38

    A, FEB, 18

    A, MAR, 45

    A, APR, 32

    A, MAY, 23

    A, JUN, 38

    A, JUL, 36

    A, AUG, 18

    A, SEP, 45

    A, OCT, 31

    A, NOV, 24

    A, DEC, 19

    B, JAN, 41

    B, FEB, 24

    B, MAR, 12

    B, APR, 17

    B, MAY, 29

    B, JUN, 15

    B, JUL, 43

    B, AUG, 49

    B, SEP, 49

    B, OCT, 33

    B, NOV, 37

    B, DEC, 28

    C, JAN, 11

    C, FEB, 19

    C, MAR, 19

    C, APR, 14

    C, MAY, 18

    C, JUN, 12

    C, JUL, 12

    C, AUG, 18

    C, SEP, 20

    C, OCT, 14

    C, NOV, 12

    C, DEC, 15

];

Left Join (Table)

LOAD Month(Date#(Month, 'MMM')) as Month,

ExchangeRate;

LOAD * INLINE [

    Month, ExchangeRate

    JAN, 44

    FEB, 16

    MAR, 43

    APR, 14

    MAY, 45

    JUN, 24

    JUL, 49

    AUG, 20

    SEP, 19

    OCT, 20

    NOV, 36

    DEC, 31

];

Concatenate (Table)

LOAD 'C as % of A' as Dim,

Month,

Num(Sum(If(Dim = 'C', Value))/Sum(If(Dim = 'A', Value)), '#.00') as Value

Resident Table

Group By Month;

Concatenate (Table)

LOAD 'B in USD' as Dim,

Month,

Num(Sum(If(Dim = 'B', Value))/Only(ExchangeRate), '#.00') as Value

Resident Table

Group By Month;

View solution in original post

8 Replies

Re: Append Calculated Data To An Existing Table

Would you be able to share the above data in the Excel format?

anuragsapanbhar
Contributor

Re: Append Calculated Data To An Existing Table

Sure.. Here it is.

Thank you.

Re: Append Calculated Data To An Existing Table

Check the attached

Capture.PNG

anuragsapanbhar
Contributor

Re: Append Calculated Data To An Existing Table

Hi Sunny,

Thank you and its great. But I would like to do that in load script, else I have to change the entire formula of my dashboard which will take a lot of time and my table shows Qtr and monthly view both.

Any idea how I can do this without any formula?

Re: Append Calculated Data To An Existing Table

Here you go

Table:

LOAD Dim,

Month(Date#(Month, 'MMM')) as Month,

Value;

LOAD * INLINE [

    Dim, Month, Value

    A, JAN, 38

    A, FEB, 18

    A, MAR, 45

    A, APR, 32

    A, MAY, 23

    A, JUN, 38

    A, JUL, 36

    A, AUG, 18

    A, SEP, 45

    A, OCT, 31

    A, NOV, 24

    A, DEC, 19

    B, JAN, 41

    B, FEB, 24

    B, MAR, 12

    B, APR, 17

    B, MAY, 29

    B, JUN, 15

    B, JUL, 43

    B, AUG, 49

    B, SEP, 49

    B, OCT, 33

    B, NOV, 37

    B, DEC, 28

    C, JAN, 11

    C, FEB, 19

    C, MAR, 19

    C, APR, 14

    C, MAY, 18

    C, JUN, 12

    C, JUL, 12

    C, AUG, 18

    C, SEP, 20

    C, OCT, 14

    C, NOV, 12

    C, DEC, 15

];

Left Join (Table)

LOAD Month(Date#(Month, 'MMM')) as Month,

ExchangeRate;

LOAD * INLINE [

    Month, ExchangeRate

    JAN, 44

    FEB, 16

    MAR, 43

    APR, 14

    MAY, 45

    JUN, 24

    JUL, 49

    AUG, 20

    SEP, 19

    OCT, 20

    NOV, 36

    DEC, 31

];

Concatenate (Table)

LOAD 'C as % of A' as Dim,

Month,

Num(Sum(If(Dim = 'C', Value))/Sum(If(Dim = 'A', Value)), '#.00') as Value

Resident Table

Group By Month;

Concatenate (Table)

LOAD 'B in USD' as Dim,

Month,

Num(Sum(If(Dim = 'B', Value))/Only(ExchangeRate), '#.00') as Value

Resident Table

Group By Month;

View solution in original post

anuragsapanbhar
Contributor

Re: Append Calculated Data To An Existing Table

Hi Sunny,

Thank you Very much. and sorry for late response. It really helped me.

Just out of curiosity , can it be done with out changing the table layout. What if my table has month as header as shown above. Can I do the calculation?

Re: Append Calculated Data To An Existing Table

Not sure I follow, I have month as header also?

Capture.PNG

anuragsapanbhar
Contributor

Re: Append Calculated Data To An Existing Table

Hi Sunny,

Apologies for my very late reply... Thank you so much for your help.. it really worked.