[resolved] Join two table with condition

Four Stars

[resolved] Join two table with condition

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.

Accepted Solutions
Four Stars

Re: [resolved] Join two table with condition

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, Smiley Happy


All Replies
Moderator

Re: [resolved] Join two table with condition

Hi,
You can use t<DB>Row component and execute a sql query that meets your "if" condition in it.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Join two table with condition

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. Smiley Happy
Let me know if you need further assistance.
MathurM
Four Stars

Re: [resolved] Join two table with condition

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. Smiley Very Happy
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. Smiley Very Happy.  can you please show me the screenshoot of each component's attribute? i will appreciate it so much.
Thank you.
One Star

Re: [resolved] Join two table with condition

Hi Eko,
This would be a lot of screen-shots. Smiley Tongue
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

One Star

Re: [resolved] Join two table with condition

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. Smiley Happy
MathurM
Four Stars

Re: [resolved] Join two table with condition

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.  Smiley Happy
Best regards,
Four Stars

Re: [resolved] Join two table with condition

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, Smiley Happy

Moderator

Re: [resolved] Join two table with condition

Hi  orawajar,
Thanks for sharing your solution with us.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.