Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Database lookup of null (blank) fields when loading from XLS

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

    Default Database lookup of null (blank) fields when loading from XLS

    (posted by Anonymous, edited for readeablility)
    I am loading a database table with the contents of an XLS. In most cases all the fields I need are already specified in the XLS, but some records are missing fields (blank columns). When these fields are missing, they can be looked up (database lookup) using a key field which is always specified. How can I take the results of a database lookup and use the looked up values to populate fields on the record which drives the lookup? The denormalization step did not seem to meet this need unless I am missing something.

    Example:

    If the XLS has this data:

    CUSTID CUSTNAME Transaction Amt
    1 Some Customer 1 Sell 10
    2 Some Customer 2 Buy 55
    1 Some Customer 1 Sell 5
    7 Buy 100
    2 Some Customer 2 Buy 55

    I would want to load the 4th record as follows:

    CUSTID CUSTNAME Transaction Amt
    7 Some Customer 7 Buy 100

    This requires looking up in a customer table, using the CUSTID field, the customer name at load time because it is not specified in the XLS.

    Please note that normalizing this information (i.e., removing the CUSTNAME) from this table is not an option -- at least not by me at this point.

    Thanks

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

    Default RE: Database lookup of null (blank) fields when loading from XLS

    How does the 4th data line look like?

    (1) 7 Buy 100
    (2) 7 <empty> Buy 100

    If (1) is the answer: change the XLS sheet, Kettle only supports streams of rows with the same layout.

    If (2) is the answer I have another question: is there another row with the following line in the XLS file?

    7 Some Customer 7 Buy 100

    If this is the case, you can read the file twice, send the data to a stream lookup file and enrich the data like that. It does so in memory, but I guess the amounts of rows are not that big if it comes from an XLS file ;-)

    Please note that Kettle can also fill in blanks by taking the value from the previous row in the Excel sheet. I&#39;m not sure that this helps in your case though.

    Hope this helps,

    Matt

  3. #3
    Join Date
    Jul 2010
    Posts
    1

    Default

    A small tips for everyone (thnx Matt!):
    in the Excel input you have to set "Repeat" to "Y" in the column you want to be filled in with the previous row content (in case it's empty).

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.