Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: optional database Lookup

  1. #1

    Default optional database Lookup

    Hi
    I have an input file where the column is sometimes blank, when I write the transformation I am trying to filter where if the column is not blank I will look up (key) value in a table, if it is blank I do not want to look up anything.
    When I tried running this it wrote out all the rows that had a value, but if you didn't have a value it didn't write out the row.
    I want my input count to equal my output and where you didn't have a value to look up leave it null, kind of like an outer join.

    Thanks

    Rod

  2. #2
    Join Date
    Sep 2011
    Posts
    190

    Default

    How about using a Switch / Case step?

    This will split your stream in two streams (in this case): one stream that has got the value and where you do the lookup for it, and one stream that needs no processing at all. In the following step in your stream you merge the two streams back into one stream.

  3. #3

    Default

    Quote Originally Posted by mbvt View Post
    How about using a Switch / Case step?

    This will split your stream in two streams (in this case): one stream that has got the value and where you do the lookup for it, and one stream that needs no processing at all. In the following step in your stream you merge the two streams back into one stream.
    Thanks
    I was able to use the Filter . I must have had a syntax error in 'not = spaces' condition, I tried it again and it works fine.

    Rod

  4. #4
    Join Date
    Sep 2011
    Posts
    190

    Default

    Filter. I hadn't thought of that. Very nice solution indeed!

  5. #5

    Default

    Dear mbvt,

    I'm searching for "Database lookup" or/and "Dynamic sql row" examples because I've to load HL7 data from files (subsystems datalog) into different tables (based on segment types) and a didn't use properly "Database lookup" and "Dynamic SQL row" tools. As I read your conversation I'm thinking about using a(n) select/case or If/then for decide that new incomming row (wich has ex. SSN for Patients) has or hasn't insert into table.

    If you can (and have enough time) help me with example(s) or instruction(s),please.

    Thanks in edvance,
    Török László

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.