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.
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
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.