Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to sum two tables?

Dear Experts,

            I would like to sum Two rows in different tables that have the same ID numbers. The other rows that are not the same IDs must be show in their order like sheet3 in my attachment.

Regards,

Kyaw Myo Tun

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: How to sum two tables?

Just concatenate the tables into one table:

Data:

LOAD Name,

    ID,

    Amount,

    'Sheet1' as Source

FROM

comm173177.xlsx

(ooxml, embedded labels, table is Sheet1);

Concatenate(Data)

LOAD Name,

    ID,

    Amount,

    'Sheet2' as Source  

FROM

comm173177.xlsx

(ooxml, embedded labels, table is Sheet2);

Then create a straight table or pivot table object and use ID as dimension and sum(Amount) as expression.


talk is cheap, supply exceeds demand
6 Replies
Employee
Employee

Re: How to sum two tables?

Just load Sheet1 and Sheet2, and the tables will concatenate into one. Then you can just do Sum(Amount).

You should order on ID, I guess.

MVP & Luminary
MVP & Luminary

Re: How to sum two tables?

Just concatenate the tables into one table:

Data:

LOAD Name,

    ID,

    Amount,

    'Sheet1' as Source

FROM

comm173177.xlsx

(ooxml, embedded labels, table is Sheet1);

Concatenate(Data)

LOAD Name,

    ID,

    Amount,

    'Sheet2' as Source  

FROM

comm173177.xlsx

(ooxml, embedded labels, table is Sheet2);

Then create a straight table or pivot table object and use ID as dimension and sum(Amount) as expression.


talk is cheap, supply exceeds demand
sushil353
Honored Contributor II

Re: How to sum two tables?

Hi,

Simply concat your table and then aggregate them by id

TableA:

Load * from TableA;

Load * from TableB;

noconcatenate

Tablec:

Load

sum(Amount) as Amount

ID,

Name

Resident TableA

group by Name,ID;

Drop talbe TableA;

HTH

sushil

MVP & Luminary
MVP & Luminary

Re: How to sum two tables?

Hi,

Please find attached file for solution.

Regards,

Jagan.

Not applicable

Re: How to sum two tables?

Dear Experts,

         We are trying to use license versions at the moment. I am using the personal edition now. so, let me request to share by script on task pane and your .qvw is not working on mine.

Thanks and Best Regards,

Kyaw Myo Tun

MVP & Luminary
MVP & Luminary

Re: How to sum two tables?

Hi,

This is the script in the attached file

Data:

LOAD Name,

     ID,

     Amount

FROM

[JOIN.xlsx]

(ooxml, embedded labels, table is Sheet2);

Concatenate(Data)

LOAD Name,

     ID,

     Amount

FROM

[JOIN.xlsx]

(ooxml, embedded labels, table is Sheet1);

Now in chart use like below

Dimension: ID, Name

Expression : Sum(Amount)

Hope this helps you.

Regards,

Jagan.