Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: Want to convert Rows to column.

  1. #1

    Question Want to convert Rows to column.

    Hi,

    Can anyone help me in building the logic in which I have to convert rows to column.
    Please check the attached file it is not simple as it looks in query.

    I have created sample input and required output in the sample file.
    Please help.

    InputOutputSample.ktr

    You can also modify the attached with solution.

    Thanks in advance,
    g_nish
    Last edited by Nishank Goel; 07-07-2016 at 11:05 AM.

  2. #2

    Default can we run a loop in Pentaho using java?

    Hi I want to know can we execute java/javascript to generate key like

    Input is:

    Data Value
    C_ID 1
    Name xyz
    age 12
    Phone 99999
    C_ID 2
    Name pqr
    Age 14
    Phone 99988
    C_ID 3
    Name qwe
    Age 90
    Phone 00000
    C_ID 4
    Name asd

    It is not necessary that the group of data (Name,Age,Phone) is fixed. It can vary for per C_ID. I want Output like this:

    Data Value Key
    C_ID 1 1
    Name xyz 1
    age 12 1
    Phone 99999 1
    C_ID 2 2
    Name pqr 2
    Age 14 2
    Phone 99988 2
    C_ID 3 3
    Name qwe 3
    Age 90 3
    Phone 00000 3
    C_ID 4 4
    Name asd 4

    I am thinking to create this in java step using the logic like if the Data = C_ID then Increase key =+1. Is it possible in pentaho.
    You can also provide some other way to do.
    Its would be appreciable if you attach with ktr.

    Thanks in Advance.
    g_nish

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

    Default

    How is this different from your existing thread?
    http://forums.pentaho.com/showthread...or-Denomaliser

  4. #4

    Smile

    Hi Gultez,

    Thanks for your quick response on this.

    The logic which you have suggested in the last attached example is not working due to another scenario identified in the file.
    The Pain point is to identify the different C_ID with there group (Name,Phone,city) the group in the braket can be any number like:

    If you check for the CustomerID 1 we have 4 sets of (Name,Phone,city) and for customerID 2 we have only 2 sets of (Name,Phone,city). This set number can be any number of sets for every CustomerID.
    Data Value
    Customer_ID 1
    Name ABC
    addresss 10 L
    Phone 1111
    Name PQR
    addresss 11 B
    Phone 99999
    City US
    Name XYZ
    Name QWE
    address 17 P
    Customer_ID 2
    Name ABC
    addresss Y
    Phone Z
    Name P
    addresss Q
    Phone R
    City S
    Customer_ID 3
    Name ABC
    Customer_ID 4
    Name L
    addresss M
    Phone N
    City O

    Hope its understanding.
    I am also attaching you the input output format.
    InputOutputSample.ktr

    Thanks,
    g_nish
    Last edited by Nishank Goel; 07-08-2016 at 03:41 AM. Reason: Describe

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

    Default

    Not all that different.
    You need to develop two keys rather than one.

    Your next step is to convert the data so that it looks like:

    Customer Data Value
    1 Name ABC
    1 address 10 L
    1 Phone 1111
    1 Name PQR
    1 address 11 B
    1 Phone 99999
    1 City US
    1 Name XYZ
    1 Name QWE
    1 address 17 P

    And then you can do the same thing as in your other thread, to convert to:



    Customer Name Data Value
    1 1 Name ABC
    1 1 address 10 L
    1 1 Phone 1111
    1 2 Name PQR
    1 2 address 11 B
    1 2 Phone 99999
    1 2 City US
    1 3 Name XYZ
    1 4 Name QWE
    1 4 address 17 P

    Then you can Denormalize it based on Name key.

  6. #6

    Default

    Hi Gultez,

    Thanks for your productive response.

    But I dn't have Name key Field to de-normalize. Can we generate it ?

    Can you show me one sample.
    Last edited by Nishank Goel; 07-13-2016 at 10:13 AM. Reason: Correction in doubt

  7. #7
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    If you denormalise data, then rows are grouped and reduced.
    -- Mick --

  8. #8

    Default

    Hi Mick,

    Can we generate Name key field present under second table from first table mentioned in the previous query ?

    Thanks,
    g_nish
    Last edited by Nishank Goel; 07-13-2016 at 10:17 AM.

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

    Default

    Quote Originally Posted by Nishank Goel View Post
    But I dn't have Name key Field to de-normalize. Can we generate it ?
    Yes, you can. Rather -- you must.

  10. #10

    Default

    Yes I have tried but not able to create. Can you help me in generating key?

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

    Default

    Quote Originally Posted by Nishank Goel View Post
    Can you help me in generating key?
    This isn't a "give me the codes" type site.

    You were given a solution to how to generate a key value for data of the form:
    Column Value
    Name Test
    Phone 123
    Name Anew
    Phone 456
    Name Something
    Phone 789

    To get:
    Column Value Key
    Name Test 1
    Phone 123 1
    Name Anew 2
    Phone 456 2
    Name Something 3
    Phone 789 3


    Since you already have this information, generating the key that you need should be straightforward.
    **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

  12. #12

    Default

    Hi Gultez,

    Not a problem.

    I am able to do it by try and run.

    Thanks,
    g_nish

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.