In table calculations

[ericdelaqua]

Hello ,

I need some ideas on how to achieve the final table below giving the first 2 tables. I have a subcategory table and a second table to categories and how they values should be calculated. I am trying to achieve a single flat table containing all the categories as shown on the 3rd table below.

TABLE 1:

Sub-category	Value
alpha	12
betta	34
zebra	-54
echo	65
romeo	82
sam	-1

TABLE  2:

Category	value
laptop	alpha-echo
Bulding	Zebra + Laptop-(sam)

Expected Output:

Listing	Value
alpha	12
betta	34
zebra	-54
echo	65
romeo	82
sam	-1
laptop	-53
Bulding	30

thanks You

[avkeep01]

Hi @ericdelaqua, 

You could try to add the table 2 to table 1 in the script. 

CONCATENATE (Table1) LOAD 
Category AS [Sub-category],
value AS Value
FROM Table2; 

In case the values aren't numbers in table 2 you can use this: 

CONCATENATE (Table1) LOAD 
'Laptop' AS [Sub-category],
SUM(IF([Sub-category],'alpha',Value,-1*Value)) AS Value
RESIDENT Table1
WHERE MATCH([Sub-category],'alpha','echo');

CONCATENATE (Table1) LOAD 
'Bulding' AS [Sub-category],
SUM(IF([Sub-category],'sam',Value,-1*Value)) AS Value
RESIDENT Table1
WHERE MATCH([Sub-category],'zebra','Laptop','sam');