Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: How To Perform a LOOK UP between DB and EXCEL

  1. #1
    Join Date
    Sep 2005
    Posts
    1,403

    Default How To Perform a LOOK UP between DB and EXCEL

    Hi, I have been trying for 3 hours how to do some lookup and apparently, I failed.

    I decided to ask for some help so that someone may throw some suggestions to help me in my problem...

    Data Sources:
    MySQL DB Table
    EXCEL SHEET (LOOKUP TABLE)

    GOAL:
    output in text the result.

    DB is connected to STREAM LOOKUP
    EXCEL is connected to STREAM LOOKUP
    STREAM LOOKUP is connected to TEXT OUTPUT

    DB contents

    CLNO--CLIENT NAME------------EARNINGS
    1---------McDonalds EUROPE---30
    2---------Walmart Asia-------------32
    3---------Walmart RUSSIA--------34
    4---------McDo USA-----------------23
    5---------Walmart USA------------12
    6---------Mcdo Canada------------21

    LOOKUP TABLE in EXCEL

    CLID--GROUP----------------CNO
    1--MC DONALDS CORP---1
    1--MC DONALDS CORP---4
    1--MC DONALDS CORP---6
    2--WALMART COMPANY---2
    2--WALMART COMPANY---3
    2--WALMART COMPANY---5

    In the STREAM LOOKUP

    Source = EXCEL LOOKUP TABLE
    Field = CLNO (of DB)
    Lookup Field = CNO (of Excel)
    Fields To Retrieve = CLID, GROUP, CNO, Earnings

    I think what I am doing is logical and it should work. However, when I run the preview or run, what happens is it outputs...

    CURRENT OUTPUT
    ->> CLNO--CLIENT NAME--EARNINGS--CLID--GROUP--CNO

    >>The columns from CLNO to EARNINGS have values while the CLID to CNO contains nothing.<<

    DESIRED OUTPUT
    CLID--GROUP------CNO--EARNINGS
    1--------MCDO--------1---------30
    1--------MCDO--------4---------23
    1--------MCDO--------6---------21
    2--------WALMART--2---------32
    2--------WALMART--3---------34
    2--------WALMART--5---------12


    /*
    The individual company names will be grouped into Major Company Groups. So something like Mcdo Asia, Mcdo Europe and Mcdo USA will be grouped under one Major Company Group called MC Donalds Corporation (MCDO GROUP). The sub company names (Mcdo Asia, Europe and USA) may not contain any word from the GROUP NAME. sub company names like Bill Gates&#39; Breakfast Supplier maybe MCDO so it should be under MCDonalds GROUP.
    */

    I am using KETTLE for two days and I will be glad to master this tool. I will be doing additional tutorials so people like me (slow learners) will gain better understanding of this great tool.

    Thanks!

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: How To Perform a LOOK UP between DB and EXCEL

    The steps you&#39;re following seem and probably are logically correct.

    Some possible causes...
    Maybe there is some kind of data-type conversion going on behind the screens? Normally this being taken care off, but with Excel you never know ;-)
    Note that CLNO and CNO are 2 different names. Did you specify this in StreamLookup?
    Do a preview on the ExcelInput step to see what kind of data is being sent to the StreamLookup step.

    HTH,

    Matt

  3. #3
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: How To Perform a LOOK UP between DB and EXCEL

    So are you saying that when I use MSSQL and with MYSQL as my lookup table, I should not experience this problem?

    I placed the column in the lookup table into the lookup field.

    Another question... I really have trouble looking for errors. Can you tell me how to look for errors. The log file just says that there is something wrong.

    I can visualize what should be done in my ETTL project but I still do not have a good understanding of the software. The software seems to resist me, hehe.

    Thanks!

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: How To Perform a LOOK UP between DB and EXCEL

    >So are you saying that when I use MSSQL and with MYSQL as my lookup table, I >should not experience this problem?

    How can I know this? You didn&#39;t tell me this before. Many people attribute magical powers to me. They are only partially correct though ;-)

    Hey, did you try the "Verify Transformation" button in the toolbar?
    Or the "Transformation / Verify" menu item?

    Perhaps this can give you a good idea of the problem.
    Also, the "Show error lines" only shows the error lines, look in the logfile just below the error. I know it&#39;s an issue with longer database errors, it will be fixed in the future when time permits...

    Cheers,

    Matt

  5. #5
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: How To Perform a LOOK UP between DB and EXCEL

    I will continue working on this thing. I hope I will succeed. Wish me luck. Thanks.

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: How To Perform a LOOK UP between DB and EXCEL

    The errors thing has been fixed in development.
    The auto-build system is down at the moment, but new builds will be available shortly that include the fix.

    Good luck!

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.