Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: two table comparison and count

  1. #1
    Join Date
    May 2008
    Posts
    1

    Default two table comparison and count

    Hi. im a beginner in kettle and i dont know how to parse. i hope you can help me with this.

    The situation goes: i have two tables
    1. Table 1: Contains a "name" field which contains First name, middle initial (with period), last name, and age.
    2. Table 2: Contains 4 separate fields for first_name, last_name, middle initial (with no period) and age.

    ex:

    Table 1
    ID | Name
    1 | Jessica A. Alba, 26

    Table 2
    ID | first_name | last_name | middle_initial | age
    1 | Jessica | Alba | A | 26

    Now i want to check if rows from table 2 will match the whole name field in table 1, with a successful match bearing 1 count.
    To do this i want the step to compare Name from table 1 and first_name, last_name, middle_initial, and age from table 2

    If Table1.Name CONTAINS %first_name%
    AND Table1.Name CONTAINS %last_name%
    AND Table1.Name CONTAINS %middle_initial%
    AND Table1.Name CONTAINS %AGE%

    then INSERT Table 2 ID #1 to Table 3 (a new table)

    then ill just SELECT COUNT table 3.

    The question is how to execute the CONTAINS part...

    Id appreciate your help anyone!

    tnx
    Dr. Ryan
    Last edited by banz69; 06-15-2008 at 08:56 AM.

  2. #2

    Default

    Why not just compare on the ID? If it exists, then the rest of the record exists as well.

    In case you are doing this just for practice, then I would use something along the lines:
    1. select FIRST_NAME || ' ' || MIDDLE_INITIAL || '. ' || LAST_NAME || ', ' || AGE from TABLE_2
    2. Compare result from step 1 with rows from TABLE_1. Match increments the counter, fail does not. This step can be implemented in many ways - one is to use a DB Lookup on TABLE_1 on the NAME column.
    You would have to take care of exceptions depending on how dependable the data from TABLE_1 is. For example what happens if a customer does not have a middle initial? You would need to modify step 1 so that it does not include the unnecessary dot and space.

    Consider using the Modified JavaScript step, if you need to put in any complex logic like the middle initial case or missing age, etc.

  3. #3
    Join Date
    Sep 2007
    Posts
    834

    Default

    You could try with "Database value lookup", using the comparator LIKE
    regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.