Skip to Content
author's profile photo Former Member
0
Former Member

Compare one column value of a table with two column values in another table

Posted on Nov 17, 2011 at 03:41 PM 458 Views

Hi all,

In Data Services can we compare one column value of a table with two column values of another table in profiling??

For ex : In table A sponsor description

In table B sponsor description, acronym.

In table A sponsor description column will have descriptions along with acronyms

Now I want to perform relationship analysis to find the non matching values in both columns of table B

I joined two tables on tableA.sponsor description= tableB sponsor description, acronym.

The result is 100% non matching values in both tables which is incorrect.

There are few common records in both the tables.

How do I get this???

Thanks

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Pedro Fernandes
    Pedro Fernandes
    Posted on Nov 17, 2011 at 04:23 PM
    0

    Hi John,

    Let me see if I understood your question. The field you want to compare to has a string separated by a comma, and then a acronym, right? For instance, 'this is some description, XYZ'. If you have a comma, then you can build a custom function to trim the string based on this pattern. You might have to output this conversion into a new field inside a query, so you can propose join.

    I suggest you start from here.

    Hope it helps,

    Pedro

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 17, 2011 at 04:28 PM
    0

    Hi

    The profiling is Data Services is useful, although fairly limited. It would be easy to do what you want with a dataflow though.

    If you need to do more complex profiling, you should use SAP's new product Information Steward! 😊

    Michael

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 18, 2011 at 05:03 AM
    0

    Either you will have to split the values of column in table A into two columns, and store in TableA_staging. Do profiling of TableB and TableA_staging.

    OR

    Concatenate both the column values of TableB into a single column and store it in TableB_staging and profile with that of tableA.

    Both can be done using query transform. Make sure the common values of both the tables are same.

    Regards,

    Suneer

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member
      Nov 21, 2011 at 10:25 AM

      Ok... You can try this...

      Take a union set of the two column values of Table B into one column in a staging table.

      ie.

      In a Data Flow, place Table B as the source. Table B should be feeding two query transforms.

      Query1 maps the Sponsor Name into Schema Out. Do not map the acronym field.

      In Query2, map the acronym field to Schema Out. Do not map the other field to schema out.

      Maintain the name, datatype and length of the schema out column in both the query transforms the same.

      Place a merge transform with input from both these query transforms, and load into a staging table - say TableB_STG.

      Now, TableB_STG has the data of both columns of TableB in a single column.

      Profile this column data with that of Sponsor Name in TableA.

      Regards,

      Suneer

      Edited by: suneer333 on Nov 21, 2011 2:16 PM

    Comment on This Answer

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.