how to sum from two tables

Report Inappropriate Content · ‎2011-10-11

Hi All,

Please i have a question

i have a field called ABC in table called table1

and field called XYZ in table called table2

i have for each item in this 2 tables to sum the sales

some items are the same in the 2 sheets

and i want a table which sum the sales for similar items

i.e

i have

ABC	Sales
Raw Material	200
Medicine	300
Wages	150
Others	400
Depreciation	50

and i have

XYZ	Sales
Raw Material	300
Wages	200
Depreciation	400

what i want is to make chart straight table for example

and to have the items in table1 and table2 are summed

the final table i want should be

anything	Sales
Raw Material	500
Medicine	300
Wages	350
Others	400
Depreciation	450

How can i do that??

Thanks in Advance

Best Regards,

Mona

Report Inappropriate Content · ‎2011-10-11

To get the sum u need to link this two table on basis of common field i.e in u r example

xyz is would be linked to anything and then take the sum of sales in chart i.e Sum(sales)

Thanks

Vivek

Report Inappropriate Content · ‎2011-10-11

Hi,

To get the sum of sales for both fields you can join the two tables on the "anything" and "Sales" fields in the script. For Example:

"table1"

Load ABC AS "anything",

Sales

From table1;

JOIN("table1")

LOAD XYZ AS "anything",

Sales

From table2;

A simple sum(sales) on the front end by the "anything" dimension should then solve this problem.

Regards GJ

Sokkorn · ‎2011-10-11

Hi Moha,

Load your data like this

[ABC]:
LOAD * INLINE [
ABC,            Sales
Raw Material,    200
Medicine,        300
Wages,            150
Others            400
Depreciation,    50];
[XYZ]:
CONCATENATE(ABC)        // Rename field XYZ to ABC (Make sure all the field name in table ABC and XYZ are the same)
LOAD * INLINE [
ABC,            Sales
Raw Material,    300
Wages,            200
Depreciation,    400];

Then in your chart use ABC as Dimension and SUM(Sales) as Expression.

Let me know if this one help you.

Regards,

Sokkorn

Report Inappropriate Content · ‎2011-10-11

i have no field called anything

i want a method to generate something like that

Report Inappropriate Content · ‎2011-10-11

hi,

i want to say that link the two transaction tables on basis of common key field i.e in u r example it is

ABC -> XYZ

i.e

[Tab1]: LOAD * INLINE [ ABC, Sales Raw Material, 200 Medicine, 300 Wages, 150 Others 400 Depreciation, 50]; CONCATENATE(ABC) // Rename field XYZ to ABC (Make sure all the field name in table ABC and XYZ are the same) LOAD * INLINE [ ABC, Sales Raw Material, 300 Wages, 200 Depreciation, 400];

U can also concatenate thses two field to get the result

Tab2:

Load

ABC as Anything,

Sum(Sales) as Sales

Resident Tab1 group by ABC;

Drop Table Tab1;

Let me know if this one help you.

Regards,

vivek

Report Inappropriate Content · ‎2011-10-11

I don't have column called sales

Sales is a set analysis expression in the chart

and the field ABC is repeated in many tables so i can't join it from one table and left the others

So,What's the solution??

Anonymous · ‎2011-10-11

do you want to solve this on script side (during the load),

or do you want to solve this on the front-end in the table itself?

Report Inappropriate Content · ‎2011-10-11

i prefer to solve it in the table itself

Anonymous · ‎2011-10-11

in the expression you should add an if construction like something like:

what connection on scripting side do the two tables have? (could you give a more detailed example on this).

this way it is easier to say what should be done at the front-end table.

rgrds.

Anita

Related Topics