Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Newbie: Output table, new input

  1. #1
    Join Date
    Nov 2007
    Posts
    3

    Default Newbie: Output table, new input

    I think it it a newbie question, but I didn't find any solution in the forum. I want to do the following:

    1. Input Table
    2. Output Table, which contains some data of input table
    3. Need the result of output table as lookup for new output table which contains also data of first input table

    Is this use case possible?

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    I don't see why not... although I don't 100% get what you want.

    With database lookup step or database join step you can lookup data from a table.

    Regards,
    Sven

  3. #3
    Join Date
    Nov 2007
    Posts
    3

    Default

    Thanks for your quick reply.

    Here is an example, what I mean:

    source table PersonSrc:

    name varchar(100),
    city varchar(100)
    ...

    target tables:
    PersonDest:
    name varchar(100)
    fkAddress int(11) (foreign key to Address)
    ...

    Address:
    pk int(11) (primary key)
    city varchar(100)
    ...

    usecase:
    1. create PersonTarget row and set name attribute with PersonSrc.name
    2. create Address row, set attribute city with PersonDest.city
    3. set attribute fkAddress of created PersonTarget row with attribute id of created Address row

    I have no idea, how to do this. Thanks for any proposal.

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    I would do it in 2 steps:
    1) run through all your data and create address rows (together with surrogate id)
    2) run through your data again, lookup up (databse lookup) the surrogate id from addresses and create a person row from that.

    There are of course lots of open points but that all depends on your specific requirements... are you just overwriting data, do you want to keep history, ...

    Regards,
    Sven

  5. #5
    Join Date
    Nov 2007
    Posts
    3

    Default

    Thanks Sven for your answer, it was and is an good starting point. But I have one additional question:

    I created an input table (quering all city and name attribute), using combined lookup/update and created the Address table with an output object (with table Mapping; using only city, because there is no field name for Address). After that step I have the lookup data needed for setting relation between Address and Person. This steps works fine.

    After the step 'output table' you lost the stream variable name which I need for creating Person. My solution is creating a new input table (querying name + data need for lookup) and then doing the steps necessary for Person creation. I dont like this solution because I have to query the database again for data which I already have.

    Is there any possibilty to save stream variables beyond an table output?

    Regards
    Stefan

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    Quote Originally Posted by chobbes View Post
    Is there any possibilty to save stream variables beyond an table output?
    No, only the ones that you save in table output. There's still a request open to allow the column names to be used in table output while outputting all of them.

    Just run your input query twice (preferably in 2 different transformations)

    Regards,
    Sven

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.