topic Re: [resolved] Join two table with condition in Talend Studio

[resolved] Join two table with condition

orawajar — Wed, 10 Dec 2014 08:25:13 GMT

Hi master,
i want to join table Store and table Reference, then create Table Result by adding one more column to table Store as shown below :

The condition is :
if StoreCode1 match with StoreCode2, and Type1 match with Type2, i need to compare Number and Group Number, if the Number contain in GroupNumber, i will add the Value to ValueResult.

for example , see row 1 table Store,
the StoreCode "001" and Type "AAA" match with row 1 and row 2 from table Reference. But because Number "1" is between 1-5 (GroupNumber), so i add Value "1" (row 1 from table Reference) to ValueResult.
Can i do this with Talend?
Thanks before.

Re: [resolved] Join two table with condition

Anonymous — Thu, 11 Dec 2014 03:55:15 GMT

Hi,
You can use t<DB>Row component and execute a sql query that meets your "if" condition in it.
Best regards
Sabrina

Re: [resolved] Join two table with condition

Anonymous — Thu, 11 Dec 2014 07:54:41 GMT

Hi Eko,
The way I would go about it, is as follows:

1. First, I would extract the limit range from TABLE_REFERENCE as 'low_value' & 'high_value' (extracting them from string "1-5" into integer values 1 & 5, for further comparison). The output of this, I would store in a hash
2. Then perform an inner join of the output in hash with TABLE_STORE on 'store_code' & 'type' keys.
3. Then, check the incoming values from TABLE_STORE with the low & high limit values. If 'number' lies within the range, assign the value from TABLE_REFERENCE, else a zero.
4. In present scenario, since we have 2 range conditions for each unique pair of 'store_code' & 'type', the tMap join would give double the number of input rows from TABLE_STORE.
5. to eliminate redundancies, I used a tAggregateRow component. If required, if the order of records is important in the output, we can use a tSortRow component.
The output I received, is as desired:

I've tried to outline the approach. Hope this helps.
Let me know if you need further assistance.
MathurM

Re: [resolved] Join two table with condition

orawajar — Fri, 12 Dec 2014 09:40:45 GMT

Thanks xdshi for your quick answer, im sorry i forgot to tell you that my table is in csv file. if you dont mind i will try mathurm solution first.
Hi mathurm, Thanks for your solution. i understand the outline but still hard for me to follow your instruction using talend. i am a beginner here. . can you please show me the screenshoot of each component's attribute? i will appreciate it so much.
Thank you.

Re: [resolved] Join two table with condition

Anonymous — Fri, 12 Dec 2014 10:31:19 GMT

Hi Eko,
This would be a lot of screen-shots.
Let me try,
The first sub-jobgoes like this:
1. TABLE_REFERENCE input : to read the reference file
2. tMap2: to extract the range

3. tHashOutput: to store the output of tMap2

Re: [resolved] Join two table with condition

Anonymous — Fri, 12 Dec 2014 10:55:53 GMT

The 2nd sub-job will be like :
1. TABLE_STORE: input component to read store file
2. tMap_1 : to check the range & assign values accordingly

3. tAggregateRow_1 : to eliminate duplicates (which will definitely be present)

4. (if required) tSortRow_1 : if the output rows are required to be sorted

I hope this should help you creating it at your end.
MathurM

Re: [resolved] Join two table with condition

orawajar — Mon, 22 Dec 2014 08:21:16 GMT

i am sorry i had much job last week.
Thank you mathurm for your answer. i still working on this. i will tell you if im done or have a problem with this.
Best regards,

Re: [resolved] Join two table with condition

orawajar — Tue, 30 Dec 2014 07:16:27 GMT

i thought i found a simple way to doing this join.
on tMap_2 i only need to set Match Model to All Matches
and then add filter to TableResult as shown below.

Thank you brother and sister for your help. problem solved,

Re: [resolved] Join two table with condition

Anonymous — Tue, 30 Dec 2014 07:57:20 GMT

Hi orawajar,
Thanks for sharing your solution with us.
Best regards
Sabrina