How to sum two tables?

[#1]

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

[Ralf-Narfeldt]

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 · Accepted solution]

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.

[sushil353]

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]

Hi,

Please find attached file for solution.

Regards,

Jagan.

[#6]

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]

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.