Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar

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

View solution in original post

6 Replies
Ralf-Narfeldt
Employee
Employee

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.

Gysbert_Wassenaar

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
Master II
Master II

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

jagan
Luminary Alumni
Luminary Alumni

Hi,

Please find attached file for solution.

Regards,

Jagan.

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.