Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
1 Solution
Accepted Solutions
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,
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,
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
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.
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.
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
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
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
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
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,
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,
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,
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,