topic Re: How to sum two tables? in QlikView

How to sum two tables?

Mon, 20 Jul 2015 09:10:42 GMT

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

Re: How to sum two tables?

Ralf-Narfeldt — Mon, 20 Jul 2015 09:16:21 GMT

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.

Re: How to sum two tables?

Gysbert_Wassenaar — Mon, 20 Jul 2015 09:17:40 GMT

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.

Re: How to sum two tables?

sushil353 — Mon, 20 Jul 2015 09:18:35 GMT

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

Re: How to sum two tables?

jagan — Mon, 20 Jul 2015 09:21:36 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: How to sum two tables?

Tue, 21 Jul 2015 05:42:56 GMT

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

Re: How to sum two tables?

jagan — Tue, 21 Jul 2015 05:55:05 GMT

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.