Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Transpose Grid to Key-Value Pairs

  1. #1
    Join Date
    Sep 2005
    Posts
    1,403

    Default Transpose Grid to Key-Value Pairs

    Attachment: concept_load.ktr Kettle is cool stuff. Clover ETL and Octopus are very limited. Great work!


    Here is a trick question or maybe not?



    What I am trying to do is the following (Trasmpose GRIDIN to KEVALOUT)







    GRIDIN (Excel File)



    ATTR1 ATTR2 ATTR3 ATTR4
    A B C D
    E F G H




    KEYVALOUT (Oracle Key-Value pair Table)



    KEY VALUE
    ATTR1 A
    ATTR2 B
    ATTR3 C
    ATTR4 D
    ATTR1 E
    ATTR2 F
    ATTR3 G
    ATTR4 H







    Here is what I have tried in KETTLE so far:



    1. Feed the Excel (Including Header Field with a assigned row number field)
    2. Filter Rows (with the condition row number = 1)
    3. Send True Data in Step 2 to a Row Normalizer which transposes the row headers (row number = 1) from the excel file as follows (all these rows are assigned a new field name (which I call ATTR_ID)
    ATTR_ID <-- Header/new field name/destined to be my Key field for the row denormalizer step below
    ATTR1
    ATTR2
    ATTR3
    ATTR4



    4. Send False Data in Step 2 to a Row Denormalizer along with the output of Step 3 above



    However I am stuck at this point and cannot proceed to Table Output which ideally would be my next step. I know I am doing this wrong but I dont see any other process that can help me with this. Any help will be greatly appreciated. I have also included the ETL file for review.



    Thanks.

  2. #2
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: Transpose Grid to Key-Value Pairs

    My tables did not come out good earlier hence trying again:

    GRIDIN (Excel File)



    ATTR1 ATTR2 ATTR3 ATTR4

    A B C D

    E F G H




    KEYVALOUT (Oracle Key-Value pair Table)



    KEY VALUE

    ATTR1 A

    ATTR2 B

    ATTR3 C

    ATTR4 D

    ATTR1 E

    ATTR2 F

    ATTR3 G

    ATTR4 H




    Also in Step 3:



    ATTR_ID <-- Header/new field name/destined to be my Key field for the row denormalizer step below

    ATTR1

    ATTR2

    ATTR3

    ATTR4

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

    Default RE: Transpose Grid to Key-Value Pairs

    Hi,

    So far you&#39;ve converted an excel sheet into key-value pairs with the row number and type field as unique key.
    However, what do you mean by "False" data?
    Please note that you only need to read the Excel file once: see §7.3 of the Spoon manual.
    If you want to merge 2 streams like you did in the sample concept_load transformation, make sure you supply it with the same row layout from all input streams.
    If you want to combine the 2 streams use for example Stream Lookup or a Join (cartesian product) step.

    Sorry, but I don&#39;t know what you want to accomplish. Once I know that, the solution would probably be rather straighforward ;-)

    Hope this helps,

    Matt

  4. #4
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: Transpose Grid to Key-Value Pairs

    The filter rows step provides the option to send 'True' data to one Step and 'False' data to another step.

    what I have done is sent the header row to the row normalizer and the data rows to the row denormalizer. basically here is waht I wanted to do:



    IN



    ATTR1 ATTR2 ATTR3 ATTR4

    A B C D

    E F G H




    OUT



    KEY VALUE

    ATTR1 A

    ATTR2 B

    ATTR3 C

    ATTR4 D

    ATTR1 E

    ATTR2 F

    ATTR3 G

    ATTR4 H



    Thanks again.

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

    Default RE: Transpose Grid to Key-Value Pairs

    - In the filter step, specify the condition and the 2 destinations : one for when the condition results to "True", the other for the oposite result.
    - For the conversion, use a Normalise step like you planned. There are samples in the Spoon manual to help you along there.

    All the best,
    Matt

  6. #6
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: Transpose Grid to Key-Value Pairs

    I was under the impression that you would have to DEnormalise to transfrom to key-value. The spoon documentation has good documentation on normalise but no examples on DEnormalise. Anyway I will try to normalise and let you know.

    thanks,

  7. #7
    Join Date
    Feb 2007
    Posts
    2

    Thumbs up Column to row transpose with kettle

    Hi,
    Column to row transpose will be with Row Normaliser step in spoon.

    How to this:
    1) add an text file input step for your CSV or XLS input file. Sample data as below:

    ATR1 ATR2 ATR3 ATR4
    A01 B01 C01 D01
    E02 F02 G02 H02

    2) add Row Normaliser for transposing of data, give a new type field name (FIELDNAME) and give a same name for all column items (FIELDVALUE)

    Type field : FIELDNAME

    # Fieldname Type New field
    1- ATR1 ATR1 FIELDVALUE
    2- ATR2 ATR2 FIELDVALUE
    3- ATR3 ATR3 FIELDVALUE
    4- ATR4 ATR4 FIELDVALUE

    3) add Select Values for review of your new structure. Click to button of Get fields to select, see new table as
    # Fieldname
    001 FIELDNAME
    002 FIELDVALUE

    4) Now, click to preview icon from menu to see as a result as;

    # FIELDNAME FIELDVALUE
    001 ATR1 A01
    002 ATR2 B01
    003 ATR3 C01
    004 ATR4 D01
    005 ATR1 E02
    006 ATR2 F02
    007 ATR3 G02
    008 ATR4 H02

    Good work.. All columns are at a row now.

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.