Hi!
I would like know ExtHashJoin about. How I do this:
I Have 2 tables: Cost Center and Expenses
Cost Center:
COD_Cost | Description
1 | Adminstration
11 | Paper
12 | Water
2 | Financial
21 | Loan
Expenses:
COD_Expenses | Cost | Value |
01 | 12 | 10.0
02 | 11 | 10.0
03 | 12 | 10.0
04 | 21 | 20.0
I need to do the output:
COD | Description | Value
1 | Adminstration | 30.0
11 | Paper | 10.0
12 | Water | 20.0
2 | Financial | 10.0
21 | Loan | 10.0
I think I would like put something like this on field Join:
COD_Cost like Cost ' % '
. It’s possible?
Hello filipebevi,
from your sample data, I dare to state the following assumptions:
-
There are only 2 levels of the COD costs (parent: e.g. 1 – Administration and child: e.g. 11 – Paper, 12 – Water).
-
The total value of the parent level COD cost is always a sum of its child level COD costs.
-
There is never a standalone expense value for the parent level COD cost (e.g. 08 | 1 | 30.0).
If that’s the case, feel free to utilize the attached graph as an example solution. The idea here is to fork the Expenses thread into 2 separate threads one of which would simply carry on the child level values while the other one would extract the sums of the parent values. Then the data from both threads are concatenated and joined with the CostCenter date. The type of the join is not vital in this case.
Regards,
Hi Vladimir!!!
Its ran perfect!!! thank you!!!
Some doubts:
1 - on my exemple, the cust center has only 2 levels, and for more levels? I should create more Reformat?
2 - if my table expenses had date field, how I would do?
Hi filipebevi,
the answer to your first question depends on the fact whether you would need to calculate subtotals. In other words, if there was the level 1, level 11 and a new level 111, the question would be whether you intend to calculate a subtotal amount for the level 11 as well. If the answer is yes, then, as you rightly said, you will have to add another Reformat component in between SimpleCopy and Concatenate and change the code respectively. For example:
$out.0.Cost = charAt($in.0.Cost,0) + charAt($in.0.Cost,1);
If the $in.0.Cost field equals “123”, the code above would basically output “12” to the $out.0.Cost field.
Note: I am still assuming that the total value of a parent level COD cost is always a sum of its child level COD Costs and that there is never a standalone expense value for any of the parent levels.
As far as your second question is concerned, the answer depends on the fact whether you need to account for the date in your results (e.g. calculate subtotals for date ranges etc.). If not, you would only need to read the data from the flat file properly which means you would need to change your input reading metadata to account for the date (including the respective format).
Regards,
Vladimir,
Thank you so much!!
You helped me a lot, have a nice day!
Att:
Filipe Beviláqua