One Star

NORMALIZE and DENORMALIZE

Hi All,
I'm new with Talend and maybe you can help me with my problem.
I'm not sure if I will use normalize and denormalize it. But my big problem is how to put it back to normal.
Input:
col1
123;456
123
456
Lookup values:
lkp_col1 | ret_val
123       =   a
456       =   b
Expected Output:
col1
a;b
a
b
As you can see, I need to lookup to the lookup table and get its value and return that value to original positioning. 
I try using tReplaceList and it works but I'm getting wrong output for example: IDs 305185 and 30518 with value of a and b. Im my input is 305185, its reads or outputed b5. I think tReplaceList is like contains function.
Thanks
4 REPLIES
Moderator

Re: NORMALIZE and DENORMALIZE

Hi,
You can use tMap with 2 lookups for your case.
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: NORMALIZE and DENORMALIZE

Hi,
My reply gone. However, as said in your first post, tMap with 2 distinct lookups is the way to go.
Regards,
TRF
One Star

Re: NORMALIZE and DENORMALIZE

Hi Guys,
Can you please explain more about using 2 lookups? 
These is just an example of my actual input: 123;456. It can be 123;456;123;789;111;222 so on..
Basically guys my main problem is how to relate this input with the lookup values.
Is this possible: where confirm_coc = lkp_confirm_coc and return value
confirm_coc contains:      
123;456;789
123
lkp_confirm_coc contains (one is to one not concat):
123 = a
456 = b
789 = c
Im getting:
NULL
a
One Star

Re: NORMALIZE and DENORMALIZE

Hi,
Each value must correspond to a separate column (123;456;789 mapped to col1, col2, col3).
If the maximum column is unknown or too large, multi lookups is not the best way. Have a look to tNormalize http://bekwam.blogspot.fr/2011/01/tutorial-turning-column-into-rows-with.html?m=1
TRF