Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: New Table has columns with old table rows value. Possible?

  1. #1

    Default New Table has columns with old table rows value. Possible?

    Hi all,

    I'm trying to do something really cool, but I've got to where I want to bem.

    I have this Table, let's call it Table A, with the following columns:

    • ID
      FirstName
      LastName
      Email
      Code
      Attribute1Name
      Attribute2Name
      Attribute3Name
      Attribute1Value
      Attribute2Value
      Attribute3Value


    What I want, and need, is to transform this information and pass it to a Table B where:

    • ID
      FirstName
      LastName
      Email
      Code


    is alike but:

    • Attribute1Name
      Attribute2Name
      Attribute3Name


    are columns with

    • Attribute1Value
      Attribute2Value
      Attribute3Value


    these values on them.

    So, for instance on Table A, Attribute1Name, Attribute2Name, Attribute3Name is in order, Age,Sex,Location and Attribute1Value, Attribute2Value, Attribute3Value are 52,Male,USA.

    In Table B, ColumnsValue don't exist and we have Column Age and rowvalue 52, and so on.

    Any help?

  2. #2
    Join Date
    Nov 2008
    Posts
    143

    Default

    Quite confusing...

    Correct me if I'm wrong. You want to turn:

    Code:
    Table A | ID | FirstName | LastName | Email | Code | Attb1Name | Attb2Name | Attb3Name | Attb1Val | Attb2Val | Attb3Val
    row1    | 1  | John      | Walker   | j@w.c | 1a2b | Age       | Sex       | Country   | 18       | Male     | USA     
    row2    | 2  | Jack      | Black    | j@b.c | 2c3d | Age       | Sex       | Country   | 35       | Male     | USA     
    row3    | 3  | Bill      | Clinton  | b@c.c | 4e5f | Age       | Sex       | Country   | 62       | Male     | USA
    into

    Code:
    Table B | ID | FirstName | LastName | Email | Code | Age | Sex  | Country
    row1    | 1  | John      | Walker   | j@w.c | 1a2b | 18  | Male | USA    
    row2    | 2  | Jack      | Black    | j@b.c | 2c3d | 35  | Male | USA    
    row3    | 3  | Bill      | Clinton  | b@c.c | 4e5f | 62  | Male | USA
    Right?

  3. #3

    Default

    Quote Originally Posted by renatopb View Post
    Quite confusing...

    Correct me if I'm wrong. You want to turn:

    Code:
    Table A | ID | FirstName | LastName | Email | Code | Attb1Name | Attb2Name | Attb3Name | Attb1Val | Attb2Val | Attb3Val
    row1    | 1  | John      | Walker   | j@w.c | 1a2b | Age       | Sex       | Country   | 18       | Male     | USA     
    row2    | 2  | Jack      | Black    | j@b.c | 2c3d | Age       | Sex       | Country   | 35       | Male     | USA     
    row3    | 3  | Bill      | Clinton  | b@c.c | 4e5f | Age       | Sex       | Country   | 62       | Male     | USA
    into

    Code:
    Table B | ID | FirstName | LastName | Email | Code | Age | Sex  | Country
    row1    | 1  | John      | Walker   | j@w.c | 1a2b | 18  | Male | USA    
    row2    | 2  | Jack      | Black    | j@b.c | 2c3d | 35  | Male | USA    
    row3    | 3  | Bill      | Clinton  | b@c.c | 4e5f | 62  | Male | USA
    Right?
    Exactly.

  4. #4
    Join Date
    Nov 2008
    Posts
    143

    Default

    If Attribute(1|2|3)Name will always be the same, then it's simpler then typing Supercalifragilisticexpialidocious?

    Put this in a TableInput Step:
    Code:
    SELECT ID, FirstName, LastName, Email, Code, Attribute1Value AS Age, Attribute2Value AS Sex, Attribute3Value AS Country FROM TableA
    Then, simply use a TableOutput to TableB.

    Of course, this is only true in case Attribute(1|2|3)Name are always the same.

  5. #5

    Default

    In fact, they can vary.

    But i'll try with this for now.

  6. #6
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    If there will only ever be 3 Attributes, you could do:
    Table Input
    Copy to 3 Select Values (each one doing a column rename Attb1Name -> AttbName)
    All 3 Select Values connected to a Sort Rows (Sort on ID)
    Row Denormaliser (Key AttbName)

    In this way, if Sex is Attb1, it will end up in the Sex column, same if Sex is Attb3.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  7. #7

    Default

    Any thoughts on variable attributes input ?

  8. #8
    Join Date
    Nov 2008
    Posts
    143

    Default

    If the attributes vary, perhaps you should use Row Normalizer.

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.