Wandering if there is a different way to achieve this avoiding custom SQL.
Appreciate any help on the same.
Best answer by JTreadwell
Hi Supriyo, Thank you for posting this question. I have added the photo you sent to me into your original post.
To handle this in Discovery Hub you don't you need to apply a transformation to the join column to make all characters uppercase or lowercase. Using this method you don't need to adjust the collation.
Right click on T1.A > Clone Field
Name A_UpperCase > Transform Values
Right click on T1.A_UpperCase > Field Transformation
In the bottom of the Transformation pane on the right, change the operator to "To upper" > Click Add
Perform steps 1-4 on T2.B
Now create your table relation on T1.A_UpperCase = T2.B_UpperCase.
Hi Supriyo, Thank you for posting this question. I have added the photo you sent to me into your original post.
To handle this in Discovery Hub you don't you need to apply a transformation to the join column to make all characters uppercase or lowercase. Using this method you don't need to adjust the collation.
Right click on T1.A > Clone Field
Name A_UpperCase > Transform Values
Right click on T1.A_UpperCase > Field Transformation
In the bottom of the Transformation pane on the right, change the operator to "To upper" > Click Add
Perform steps 1-4 on T2.B
Now create your table relation on T1.A_UpperCase = T2.B_UpperCase.
Wandering if there is a different way to achieve this avoiding custom SQL.
Appreciate any help on the same.
Page 1 / 1
Hi Supriyo, Thank you for posting this question. I have added the photo you sent to me into your original post.
To handle this in Discovery Hub you don't you need to apply a transformation to the join column to make all characters uppercase or lowercase. Using this method you don't need to adjust the collation.
Right click on T1.A > Clone Field
Name A_UpperCase > Transform Values
Right click on T1.A_UpperCase > Field Transformation
In the bottom of the Transformation pane on the right, change the operator to "To upper" > Click Add
Perform steps 1-4 on T2.B
Now create your table relation on T1.A_UpperCase = T2.B_UpperCase.
The resulting solution should look like this:
This should solve your problem.
Let me know if you need clarification.
Hi Joseph,
Thanks for the response. I don't want to convert all values to upper case , then there will be no difference between 'wNFq' and 'WNFQ'.
I want to implement the join based on the exact upper and lower case values with correct sequencing in upper and lower cases.
So 'wNFq' should only join to 'wNFq' and not to 'WNFQ' or to 'WnfQ'
Appreciate your help.
I see, Sorry I misunderstood.
In this case the only way to do this would be to change the collation on the whole database. OR use a custom table insert to write the custom script.
I do see how this would be a helpful feature and highly suggest you add this to Ideas section.
Hi Supriyo
I suggest hashing the join fields. With hashing you ensure that there is a difference between the to values WNFQ and WnfQ.
And if you are using more keys in you lookup you could even experience a performance improvement in the lookup.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
