How to combine 2 tables and add a new calculating column?

[evelyn1]

Hi all,

I have two original table(TABLE A and TABLE B). Now I want to create a new table on Qlik Sense. Please refer to the picture below.

The new table I'd like to create must have the columns in TABLE A and TABLE B, and I want to create a new column which calculates the difference between  TABLE A.NUMBER and TABLE B.NUMBER.

I can't put TABLE A.NUMBER and TABLE B.NUMBER in one table together. I also tried to calculate the difference between  TABLE A.NUMBER and TABLE B.NUMBER by using Aggr(SUM), but it didn't work.

Please help me, thanks!

More questions, I add Cr and Dr numbers, and want the result in pivot analysis table like picture below, but could only get the result in last pictures.

[vinieme12]

The tables need to be associated in your datamodel

then i'ts a simple =  Sum(TableA.Number)-sum(TableB.Number)

[evelyn1]

Thanks a lot for your reply.

I've associated TABLE A and TABLE B by Column "TYPE".

Sorry that I adjusted my question. The actual data would have Cr and Dr number. I tried to create a pivot analysis table like below picture, but I could only get the result in the last picture.

[stevedark]

HI @evelyn1 

You may find that concatenating the data rather than associating it makes things much easier, as the format of both tables are essentially the same.

Table:
LOAD
   TYPE,
   'TABLE A' as Table,
   Cr,
   Dr
FROM [lib://Files/TableA.qvd] (qvd);

CONCATENATE (Table)
LOAD
   TYPE,
   'TABLE B' as Table,
   Cr,
   Dr
FROM [lib://Files/TableB.qvd] (qvd);

Now you can have Table as a dimension in your pivot table.

Better still may be to create direction as a dimension, so you can add that as well. You can do this by loading Dr and Cr one at a time from each table:

Table:
LOAD
   TYPE,
   'TABLE A' as Table,
   Cr,
   0 as Dr,
   Cr as Value,
   'Cr' as Direction
FROM [lib://Files/TableA.qvd] (qvd);

CONCATENATE (Table)
LOAD
   TYPE,
   'TABLE B' as Table,
   Cr,
   0 as Dr,
   Cr as Value,
   'Cr' as Direction
FROM [lib://Files/TableB.qvd] (qvd);

CONCATENATE (Table)
LOAD
   TYPE,
   'TABLE A' as Table,
   0 as Cr,
   Dr,
   Dr * -1 as Value,
   'Dr' as Direction
FROM [lib://Files/TableA.qvd] (qvd);

CONCATENATE (Table)
LOAD
   TYPE,
   'TABLE B' as Table,
   0 as Cr,
   Dr,
   Dr * -1 as Value,
   'Dr' as Direction
FROM [lib://Files/TableB.qvd] (qvd);

Once you have loaded the data in this way you can create a pivot with TYPE, Table and Direction as dimensions and sum(Value) as the measure.

Hope that helps.

Steve

https://www.quickintelligence.co.uk/blog/