How To Perform a LOOK UP between DB and EXCEL

[kettle_anonymous]

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!

[MattCasters]

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

[kettle_anonymous]

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!

[MattCasters]

>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

[kettle_anonymous]

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

[MattCasters]

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!