Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Unusual transform

  1. #1
    Join Date
    Nov 2013
    Posts
    382

    Default Unusual transform

    This is probably the most stupid format I have ever seen, but unfortunately it's beyond our control. I have been playing with normalize/denormalize but unable to find a suitable way to do it.

    We receive rows with some non-zero values and we should produce rows with pairs of fields, one with a fixed name for every non-zero value and a second with the non-zero value itself ... and of course (this is the real problem) the non-zero pairs on the firsts fields of the row

    Input rows:

    id,V1,V2,V3,....,V20

    where some Vi are not zero.

    Output rows:

    id,'F3',V3,'F5',V5,null,0,..... (for a row with v3 and v5<>0)
    id,'F1',V1,'F7',V7,'F19',V19,null,0,... (for a row with v1, v7 and v19 <>0)


    For example:

    1,0,0,25,0,30,0,...,0
    2,100,0,0,0,0,0,70,0,...,115,0

    must become

    1,'F3',25,'F5',30,null,0,...
    1,'F1',100,'F7',70,'F19',115,null,0,...

    Any idea?

    Thks

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

    Default

    You have some CSV file with an id and a certain number (n) of Integer fields.
    You want to have a CSV file with all the non-zero fields shifted to the left.
    Also, you want to add the respective fieldnames in front of their values?
    I have no idea.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Aug 2016
    Posts
    11

    Default

    Hardly ideal but you can do it with arbitrary javascript. Or a bunch of filters and 'set values' in a row.

    Do you at least know beforehand how many incoming fields you have and what are their respective names?

  4. #4
    Join Date
    Feb 2014
    Posts
    5

    Default

    A full solution will require some moire analysis, but I would probably attempt to tackle this problem by:
    * using the "Row Normalizer" step to covert the columns of your input rows into seperate rows
    * add the names as columns to these new rows
    * sort the rows as required, using temporary columns if required
    * flatten the rows using the "Row flattener" or "row denormalizer" step

    Do your rows always have 20 columns, or can this vary?

  5. #5
    Join Date
    Nov 2013
    Posts
    382

    Default

    Thks marabu, adding a splitfield step (I need to create an sql row!!) to your example worked as expected. I tried the group solution but was "obsessed" with getting fields so didn't think about creating a line and splitting it later!

    sven, pedro: yes it's a fixed number of fields.

    PS. I guess the one who designed this format doesn't even know what the words normal and form mean

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.