Left Join with like

Support/help with CloverETL implementation problems

filipebevi
Posts: 3
Joined: Fri Jul 14, 2017 6:21 pm

Left Join with like

Postby filipebevi » Fri Jul 14, 2017 6:47 pm

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:

Code: Select all

 COD_Cost like Cost ' % '
. It's possible?

bartonv
Posts: 20
Joined: Wed May 03, 2017 12:10 pm

Re: Left Join with like

Postby bartonv » Fri Jul 21, 2017 7:45 am

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,
Attachments
Example.grf
(6.14 KiB) Downloaded 5 times
inputData.zip
(352 Bytes) Downloaded 3 times
---
Vladimir Barton
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support

filipebevi
Posts: 3
Joined: Fri Jul 14, 2017 6:21 pm

Re: Left Join with like

Postby filipebevi » Tue Jul 25, 2017 10:23 pm

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?

bartonv
Posts: 20
Joined: Wed May 03, 2017 12:10 pm

Re: Left Join with like

Postby bartonv » Fri Jul 28, 2017 3:02 pm

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:

Code: Select all

$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 Barton
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support

filipebevi
Posts: 3
Joined: Fri Jul 14, 2017 6:21 pm

Re: Left Join with like

Postby filipebevi » Thu Aug 03, 2017 1:53 pm

Vladimir,

Thank you so much!!

You helped me a lot, have a nice day!

Att:

Filipe Beviláqua


cron