Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Need to generate data values based on field input

  1. #1
    Join Date
    Feb 2008
    Posts
    216

    Question Need to generate data values based on field input

    Is there a way to do this?

    Here's my scenario...

    I have a table that contains a field that represents a starting range and a field that represents an ending range, along with some other attributes.

    Fields are as follows with some sample input:

    account_number_start, account_number_end, department_start, department_end, grouping
    000001, 000003, 0001, 0003, Sample Group 1
    000004, 000005, 0001, 0003, Sample Group 2
    000006, 000007, NULL, NULL, Sample Group 3

    I want my resulting output to look like this:

    000001, 0001, Sample Group 1
    000001, 0002, Sample Group 1
    000001, 0003, Sample Group 1
    000002, 0001, Sample Group 1
    000002, 0002, Sample Group 1
    000002, 0003, Sample Group 1
    000003, 0001, Sample Group 1
    000003, 0002, Sample Group 1
    000003, 0003, Sample Group 1
    000004, 0001, Sample Group 2
    000004, 0002, Sample Group 2
    000004, 0003, Sample Group 2
    000005, 0001, Sample Group 2
    000005, 0002, Sample Group 2
    000005, 0003, Sample Group 2
    000006, NULL, Sample Group 3
    000007, NULL, Sample Group 3

    So I want to fill in records for the values between the range and have a record for each combination of the account number and the department number range.

    Could anyone recommend a set of steps that will allow me to generate the values for records in between the ranges? I looked at the normalizer, but it looks like you still need to have the data values to pivot in the first place. I need to actually create the data itself.

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

    Default

    Javascript

  3. #3
    Join Date
    Feb 2008
    Posts
    216

    Default

    Quote Originally Posted by sboden View Post
    Javascript
    I tried using a javascript with a do while, but I only end up with the end value and not the in between values... How do I generate records with a javascript?

    I tried using an array as well, but it doesn't appear the Pentaho supports this?
    Last edited by DebbieKat; 06-18-2008 at 02:08 PM.

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

    Default

    Arrays are not supported

    For your functionality... look in the samples directory to:
    - JavaScript - create new rows
    - JavaScriptMod - skip rows after x rows.

    What you want is a combination of those (the second to skip the final record).

    Regards,
    Sven

  5. #5
    Join Date
    Feb 2008
    Posts
    216

    Default Thanks Sven!

    Quote Originally Posted by sboden View Post
    Arrays are not supported

    For your functionality... look in the samples directory to:
    - JavaScript - create new rows
    - JavaScriptMod - skip rows after x rows.

    What you want is a combination of those (the second to skip the final record).

    Regards,
    Sven
    That looks like what I need.

  6. #6
    Join Date
    Feb 2008
    Posts
    216

    Default Maybe I spoke too soon?

    It looks like the example still has all of the data that it wants to convert into records beforehand, and I don't quite have this.

    It is taking a field of comma-delimited data and making those pieces of data into separate rows. My field represents an end value of a range and I need to fill in the blanks in between the first and second field.

    Also, I'm not familiar with some of the commands in this java script example and they don't seem to exist in a standard java script reference book. Are these built in Pentaho commands?

    createRowCopy
    getOutputRowMeta().size()
    getInputRowMeta().size()
    Also, you mentioned arrays are not supported, but isn't the syntax below an array?
    newRow[rowIndex++]
    groups[i]
    This is the code from the example...

    if (groupsField!=null)
    {
    var groups = groupsField.split(",");

    for (i=0;i<groups.length;i++)
    {
    newRow = createRowCopy(getOutputRowMeta().size());
    var rowIndex = getInputRowMeta().size();

    [I] newRow[rowIndex++] = trim( groups );
    newRow[rowIndex++] = "N";

    putRow(newRow);
    }
    }

    var subgroup = "";
    var ignore = "Y";

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

    Default

    You'll have to play around a bit Debbie with your examples, I don't have the time right now to make a nice prepared example.

    For createRowCopy() and other functions... these are functions defined in PDI, the javascript step embeds the javascript engine but also supplies some extra functions (you can find those on the Pentaho wiki).

    Array support... internally in 1 javascript step you use arrays as the javascript engine supports it, the row passing system does not.... unless you go to very long ways of putting arrays in a string and passing it like that (which will give problems when you want to act upon those fields with other steps )

    For the reason why you still get the input rows... the default action of the javascript step is to do a "automatic" putRow ... besides all the putRows() you do yourself. Using the way of skipping rows from the other example you can do your own putRows() while disabling the "automatic" one.

    Regards,
    Sven

  8. #8
    Join Date
    Feb 2008
    Posts
    216

    Default Is this the right wiki page?

    This seems to be the only one I can find that mentions any of these commands:

    http://wiki.pentaho.com/display/EAI/JavaScript+Step+FAQ

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

  10. #10
    Join Date
    Feb 2008
    Posts
    216

    Default Yep...

    Quote Originally Posted by sboden View Post
    I eventually found my way to this one from the FAQ one. The title of the document threw me off.

    I was able to get this to work finally! Yay!

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.