Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Auto Increment number based on column

  1. #1
    Join Date
    Apr 2014
    Posts
    5

    Default Auto Increment number based on column

    Hi,
    I want to Generate Auto Increment number based on column. Following is a sample input data.
    STUDENT_ID CLASS DATE
    123 MATH 04/18/2014
    123 SCI 04/17/2014
    123 CHEM 04/16/2014
    456 MATH 04/18/2014
    456 GEO 04/15/2014

    Desired Output.



    STUDENT_ID CLASS DATE ROW_ID
    123 MATH 04/18/2014 1
    123 SCI 04/17/2014 2
    123 CHEM 04/16/2014 3
    456 MATH 04/18/2014 1
    456 GEO 04/15/2014 2

    My Transformation does to work with any database so I can not use ROW_NUMBER etc.
    How can I achieve this?

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    Try using the "Add value fields changing sequence" step.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Apr 2014
    Posts
    5

    Default

    Quote Originally Posted by darrell.nelson View Post
    Try using the "Add value fields changing sequence" step.
    I am not able to find that field. Are you referring to Add Sequence?

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    Quote Originally Posted by niravp View Post
    I am not able to find that field. Are you referring to Add Sequence?
    No. It is the step just below the Add sequence step, in Spoon version 4.4.0-stable anyway.

    Name:  ChangingValueSequence.png
Views: 649
Size:  15.5 KB
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5
    Join Date
    Apr 2014
    Posts
    5

    Default

    Thanks for the quick reply Derrell.
    I am using 3.2.5 and I do not have that step. Is there any work around for that?

  6. #6
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    A totally crazy approach would be: Add a costant _one = 1; let a Group By step calculate the cumulative sum of _one for group key = STUDENT_ID. Don't forget to enable "Include all rows".
    So long, and thanks for all the fish.

  7. #7
    Join Date
    Nov 2008
    Posts
    777

    Default

    You could use a JavaScript step. Be sure to sort your rows on the STUDENT_ID first though.

    Code:
    var ROW_ID;
    
    if (ROW_ID == null) {
     ROW_ID = 0;
     _previous = -1;
    }
    
    if (STUDENT_ID != str2num(_previous)) {
     ROW_ID = 1;
    } else {
     ROW_ID = ROW_ID + 1;
    }
    
    _previous = STUDENT_ID;
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  8. #8
    Join Date
    Nov 2008
    Posts
    777

    Default

    Quote Originally Posted by marabu View Post
    A totally crazy approach would be: Add a costant _one = 1; let a Group By step calculate the cumulative sum of _one for group key = STUDENT_ID. Don't forget to enable "Include all rows".
    It's not a totally crazy approach but was it available in 3.2.5? That's so far back I have no idea!
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  9. #9
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    The steps were available, Darrell, I made sure that much.
    I'm not entirely confident regarding the options in the Group By step, though.
    Let's wait and see ...
    So long, and thanks for all the fish.

  10. #10
    Join Date
    Apr 2014
    Posts
    5

    Default

    Quote Originally Posted by marabu View Post
    A totally crazy approach would be: Add a costant _one = 1; let a Group By step calculate the cumulative sum of _one for group key = STUDENT_ID. Don't forget to enable "Include all rows".
    Thanks a Lot Marabu. It Worked!!!!

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.