Hitachi Vantara Pentaho Community Forums
Results 1 to 18 of 18

Thread: Convert Columns to Rows/Rows to Columns

  1. #1
    Join Date
    Aug 2008
    Posts
    27

    Default Convert Columns to Rows/Rows to Columns

    Is there a transform to take a single row of inbound data and convert it to rows? For example, suppose the inbound row to the transform looks like:

    Name Address City
    ===== ===== ======
    John 123 Main MyTown

    The Outbound data would be

    Column Name Value
    ========= ========
    Name John
    Address 123 Main
    City MyTown

    Similarly, I need a transform to go in the reverse direction, rows back into columns.

    I need to process the columns like variables. I have to lookup each column name and value to obtain a possible translation to a new value.

    Thank you,
    Gene

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

    Default

    You have row normalization/denormalization step but they are not variable sensitive... and it doesn't for a large part make sense to make them variable sensitive.

    Regards,
    Sven

  3. #3
    Join Date
    Aug 2008
    Posts
    27

    Default

    Sven,

    I didn't mean that I need to use PDI variables for the values. I meant that the PDI application will not know the names of the columns at design time and will need to step through each column name for a possible translation of the column value at run time. By converting the columns into rows, I will be able to lookup the column name and value against an external table to obtain a translate value if one is there.

    Unless I am missing something, the examples in the samples folder dealing with row normalization/denormalization know the names of the columns at design time which will not suit my purposes.

    Thanks,
    Gene

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

    Default

    Quote Originally Posted by gstempel View Post
    Sven,

    I didn't mean that I need to use PDI variables for the values. I meant that the PDI application will not know the names of the columns at design time and will need to step through each column name for a possible translation of the column value at run time. By converting the columns into rows, I will be able to lookup the column name and value against an external table to obtain a translate value if one is there.

    Unless I am missing something, the examples in the samples folder dealing with row normalization/denormalization know the names of the columns at design time which will not suit my purposes.
    I know ... it's not in and no short term plans I think to build it in.

    Regards,
    Sven

  5. #5
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Should be possible in Javascript:

    Code:
    var rowMeta = getInputRowMeta();
    
    for (i=0;i<rowMeta.size();i++) {
      var valueMeta = rowMeta.getValueMeta(i);
      newRow = createRowCopy(getOutputRowMeta().size());
      var rowIndex = getInputRowMeta().size();
      newRow[rowIndex++] = valueMeta.getName();
      newRow[rowIndex++] = rowMeta.getString(newRow, i);
      putRow(newRow);
    }
    
    var columnName = "xx";
    var value = "yy";
    Export the columnName and value fields.
    Compatibility off.
    Filter out the xx columns.
    Only select the columnName and value columns.

  6. #6
    Join Date
    Aug 2008
    Posts
    27

    Default

    Matt,

    Thank you for your reply.

    I see that

    rowMeta.getString(newRow, i);

    retrieves the value from column i

    Is it possible to change the value of column i ?

    Essentially, the purpose of this application is to iterate through the column names and values, lookup a name/value pair in an external table to obtain a translated value, and replace the value in the column.

    Thanks again!

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Yes, you can change the row value directly as well.

    newRow[index] = foo;
    or
    row[index] = bar;

    Just make REALLY sure that the data types are the same as what the metadata says it will be. Take especially care with numeric data types. JavaScript has the nasty habit of mixing those in a bad way, turning integers to double precision etc.
    For that reason, I never recommend it. I recommend that you add new values in stead of replace existing ones. In your particular case however, replacing values might be the only way.

    All that being said, this is an extremely unusual use-case.

    Matt

  8. #8
    Join Date
    Aug 2008
    Posts
    27

    Default

    I had omitted the specifics of this use-case to keep the discussion as general as possible, but I thought you might be interested in the details of exactly what I am attempting to do.

    We have a database where various tables are segmented by customer id. When deploying a new customer, many of the lookup tables are identical to those of an existing customer. However, we can't simply export-import the data because many of the tables' primary keys are sequence generated. To further complicate matters, these primary keys appear in related tables. A simple export-import would violate uniqueness constraints, and failure to renumber the keys in both the primary tables and related tables would violate referential integrity.

    Thus, my goal is to use spoon to retrieve the data from the database, and resequence the primary keys along with the referential counterparts. This process will be table driven where the driver tables will contain the table name, primary key, original id, new id, etc.

    If you have any interest, I will post my progress on this thread. Otherwise, I will keep my postings to questions only.

    Thank you for your assistance and best regards,
    Gene

  9. #9
    Join Date
    Aug 2008
    Posts
    27

    Default

    Matt,

    I guess I am a obviously confused. I tried to update a column value with the following code:

    var
    rowIn = getInputRowMeta();

    var
    rowOut = getOutputRowMeta();

    for
    (i=0;i<rowIn.size();i++) {
    var valueMeta = rowIn.getValueMeta(i);
    var columnName = valueMeta.getName();
    newRow =
    createRowCopy(rowOut.size());
    columnVal = rowMeta.getString(rowOut, i);
    if ( columnName == "cust_id" && str2num(columnVal) == 11765 ){
    Alert("found cust_id 11765");
    newRow[i] = 11766;
    }
    }

    putRow( newRow );


    Only one row of data enters the transformation. I know that this will generate a second row, but both rows are identical. The Alert pops up, but the cust_id in the second row is unchanged from the first. How do I change the value of the outbound column, and how do I do this to the original row without adding a new row?

    I appreciate your patience and assistance

    Thanks,
    Gene

  10. #10
    Join Date
    Aug 2008
    Posts
    27

    Default

    Matt,

    I have already answered my questions. I didn't realize that row is a predefined accessible object. I modified my code as follows:

    var rowMeta = getInputRowMeta();
    var columnName;
    var columnVal;
    for (i=0;i<rowMeta.size();i++) {
    var valueMeta = rowMeta.getValueMeta(i);
    columnName = valueMeta.getName();
    columnVal = rowMeta.getString(row, i);
    if ( columnName == "cust_id" && str2num(columnVal) == 11765 ){
    Alert("found cust_id 11765");
    row[i]=11766;
    }
    }

    Of course I immediately encountered the Javascript issue you referred to earlier, i.e. 11766, as entered, is treated as a floating point instead of an int. Is there any way to force that value to an int?

  11. #11
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Don't think so.

  12. #12

    Default

    hmm, that is sounding pretty good. I had a similar issue a while ago related to excel data needing to be rotated as well (http://forums.pentaho.org/showthread.php?t=59865). That ended up being a re-key job to rotate the data manually, so no solution was found at that time.

    It's a rare, but time-consuming, problem to rotate data (particularly if it is variable on row or column length).

  13. #13
    Join Date
    Aug 2008
    Posts
    27

    Default

    I believe I am very close to achieving my objective, but have run into what may be a show stopper. As I explained in an earlier post, the resequencing of Id's I am trying to accomplish has the luxury of involving strictly integer fields. However, when I attempt to make the update, I receive the following error:

    cust_id Integer(9) : There was a data type error: the data type of java.lang.Integer object [11766] does not correspond to value meta [Integer(9)]

    The code is :

    var rowMeta = getInputRowMeta();
    var TableName = "cddval";
    var columnName;
    var columnVal;
    var sql;
    for (i=0;i<rowMeta.size();i++) {
    var valueMeta = rowMeta.getValueMeta(i);
    columnName = valueMeta.getName();
    sql = "select * from xlateTable where TableName = '"+upper(TableName)+"' and FieldName = '"+upper(columnName)+"'";
    rslt = fireToDB("sqllite",sql);
    if ( rslt[0] != null )
    {
    columnVal = rowMeta.getString(row, i);
    sql = "select NewVal from xlateValue where TableName = '"+upper(TableName)+"' and FieldName = '"+upper(columnName)+"' and OldVal = "+columnVal;
    rslt = fireToDB("sqllite",sql);
    if ( rslt[0] != null )
    row[i] = rslt[0][0]; <---------This is the line generating the error
    }
    }

    The source data is coming from a table-lookup MySql sql statement. The lookup via fireToDB() is from a SQLLite table.

    Any Suggestions?

    Thanks,
    Gene

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

    Default

    Integer Metadata is in fact Long if I remember correctly.

    Regards,
    Sven

  15. #15
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Perhaps you can in fact create a new java.lang.Long object. That would in fact correspond to the Kettle Integer data type.

  16. #16
    Join Date
    Aug 2008
    Posts
    27

    Default

    Bravo guys!

    I changed the offending line to:

    row[i] =
    new java.lang.Long(rslt[0][0]);


    and it worked!

    Thank you very much!

    The show can go on!

  17. #17
    Join Date
    Aug 2008
    Posts
    27

    Default

    One more question......

    In the earlier code examples, I am using:

    var rowMeta = getInputRowMeta();
    for (i=0;i<rowMeta.size();i++) {
    var valueMeta = rowMeta.getValueMeta(i);
    columnName = valueMeta.getName();
    columnVal = rowMeta.getString(row, i);

    This retrieves the Column Name and Value. Is it possible to retrieve the column type (number, string, date, etc.) as well?

    Thanks,
    Gene

  18. #18
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Obviously, the ValueMetaInterface objects contains all the value metadata you need as well as the definitions of the types:

    Code:
        /** Value type indicating that the value has no type set */
        public static final int TYPE_NONE        = 0;
        
        /** Value type indicating that the value contains a floating point double precision number. */
        public static final int TYPE_NUMBER      = 1;
        
        /** Value type indicating that the value contains a text String. */
        public static final int TYPE_STRING      = 2;
        
        /** Value type indicating that the value contains a Date. */
        public static final int TYPE_DATE        = 3;
        
        /** Value type indicating that the value contains a boolean. */
        public static final int TYPE_BOOLEAN     = 4;
        
        /** Value type indicating that the value contains a long integer. */
        public static final int TYPE_INTEGER     = 5;
        
        /** Value type indicating that the value contains a floating point precision number with arbitrary precision. */
        public static final int TYPE_BIGNUMBER   = 6;
        
        /** Value type indicating that the value contains an Object. */
        public static final int TYPE_SERIALIZABLE= 7;
        
        /** Value type indicating that the value contains binary data: BLOB, CLOB, ... */
        public static final int TYPE_BINARY      = 8;
    
        public int      getType();

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.