Left Join with like

filipebevi

• July 14, 2017 00:00

• Answered

Follow

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?

• 

  Vladimir Barton

  • July 21, 2017 06:45

  0

  Comment actions Permalink

  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,

  • inputData.zip
  • Example.grf
• 

  filipebevi

  • July 25, 2017 21:23

  0

  Comment actions Permalink

  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?
• 

  Vladimir Barton

  • July 28, 2017 14:02

  0

  Comment actions Permalink

  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,
• 

  filipebevi

  • August 03, 2017 12:53

  0

  Comment actions Permalink

  Vladimir,
  
  Thank you so much!!
  
  You helped me a lot, have a nice day!
  
  Att:
  
  Filipe Beviláqua

Please sign in to leave a comment.