Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Beginners question on looking up row values

  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Question Beginners question on looking up row values

    Hi,

    I'm trying to look up a value in a column data set which I'm sure is a simple procedure but I can seem to do it.

    Say I have one column flowing through the transform called "code". For each value of code, I need to find it's parent code. I do this by recursively shortening it's length by 1, re-search the total "code" column again and if found, output this shortened value in the stream as a new variable called "parentCode".

    e.g.

    code
    ====
    1 <- parentCode = NULL
    10 <- parentCode = 1
    100 <- parentCode = 10
    102 <- parentCode = 10
    1020001 <- parentCode = 102

    I've been trying to do this in the Modified Javascript step but I can't seem to get access to the complete dataset for the "code" column in order to search within it. I think I would need the complete dataset in memory before the search for parentCode is done for each row but correct me if I'm wrong.

    I tried putting the complete set in an array in the Javascript step but the array seems to be redefined on each new row. I even tried a separate preceeding Javascript step to fill the array but that didn't work either. I think the Stream input step might work but I don't know how to use it properly.

    Can someone help out a newbie?
    Thanks,
    D

  2. #2

    Default

    Hi,

    What I would do is flow to a lookup stage after you have shortened it the code. Then if found flow to another js stage that adds the shortened code as a new field/column called "first parent", shorten the code and then flow to another lookup stage followed by a js stage which stores "second parent", repeat these two stages for the max number of possible parent keys.

    The error handling from each of the lookup stages should then all combine into the final stage where you can process each of the parent keys found for that particular row.

    Hope it helps.

    Regards
    Paul

Tags for this Thread

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.