Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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');