Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Set values dynamically to step "Row Denormaliser"

  1. #1
    Join Date
    Jan 2017
    Posts
    4

    Default Set values dynamically to step "Row Denormaliser"

    Hello everybody!

    I have a requirement to pivot a table to send it as a .csv.

    My main goal is to transform the data row of a column into several columns, which one as date. The extraction will be send every monday, containing data from the last 7 days. Because the date changes, I would have to dynamically set the values for the date to become automatic.

    My Transformation is this way:

    Name:  Screenshot from 2017-01-24 10-26-41.jpg
Views: 729
Size:  6.6 KB

    My Row denormaliser is like this:

    Name:  row denormaliser.jpg
Views: 748
Size:  17.7 KB

    My query is returning something like this:

    parceiro inserted_date servico valor type
    xyz 2017-01-19 abc 9840 novos
    xyz 2017-01-19 abc 1017 cancelados
    xyz 2017-01-19 abcd 305074 base
    xyz 2017-01-19 abcd 0 novos
    xyz 2017-01-19 xpto 14 cancelados
    xyz 2017-01-19 xpto 10457 base
    xyz 2017-01-20 xpto 3100 novos
    xyz 2017-01-20 qwerty 1378 cancelados
    xyz 2017-01-20 qwerty 441693 base
    xyz 2017-01-20 qwerty 9 novos

    And the step Row denormaliser is returning what I need:

    parceiro servico type 2017-01-17 2017-01-18 2017-01-19 2017-01-20 2017-01-21 2017-01-22 2017-01-23
    xyz xpto base 46562 52744 58156 62053 64837 64682 60957
    xyz xpto cancelados 609 730 771 527 405 260 548
    xyz xpto novos 5262 6790 6140 4667 3310 250 3935
    xyz abc base 10489 10470 10457 10443 10428 10413 9181
    xyz abc cancelados 19 13 14 15 16 11 13
    xyz abc novos 0 0 0 0 0 1 0
    xyz abcd base 28605 28545 28503 28458 28413 28379 25181
    xyz abcd cancelados 60 42 46 45 36 17 53
    xyz abcd novos 2 0 0 1 0 2 1
    xyz qwerty base 290215 296255 305074 306607 307360 306720 270586


    My problem is that I need to change every week the fields Target fieldname and Key Value in Row Denormaliser step. Since I can not use variables, and I can only use one value in with step ETL Metadata Injection, what can I do?

    I am using PDI 6.1 with Linux, and the database that I am conecting is Amazon Redshift, and it does not have pivot function like SQL Server or Postgres.

    Thanks everyone!

  2. #2
    Join Date
    Jan 2017
    Posts
    3

    Default

    Do you found solution?

  3. #3
    Join Date
    Jan 2017
    Posts
    4

    Default

    Quote Originally Posted by VagnerS View Post
    Do you found solution?
    Not yet...

  4. #4
    Join Date
    Jan 2017
    Posts
    3

    Default

    Sample: module.zip

    well, the soluction to me was use to two transformation

    First I'm setting the variables and I changed the field name with a script java by method setName('FieldName")

    My Row denormaliser

    Name:  Row denormaliser.PNG
Views: 739
Size:  7.1 KB

    Name:  module.PNG
Views: 715
Size:  5.8 KB

    Field name after User Defined Java Class

    Name:  filedName.PNG
Views: 713
Size:  1.8 KB

  5. #5
    Join Date
    Jan 2017
    Posts
    4

    Default

    Quote Originally Posted by VagnerS View Post
    Sample: module.zip

    well, the soluction to me was use to two transformation

    First I'm setting the variables and I changed the field name with a script java by method setName('FieldName")

    My Row denormaliser

    Name:  Row denormaliser.PNG
Views: 739
Size:  7.1 KB

    Name:  module.PNG
Views: 715
Size:  5.8 KB

    Field name after User Defined Java Class

    Name:  filedName.PNG
Views: 713
Size:  1.8 KB
    But in my document I have other columns, not just que date columns. Can I still use it?


    I downloaded your sample but I could not reproduce it...

  6. #6
    Join Date
    Jan 2017
    Posts
    3

    Default

    Quote Originally Posted by iagoxmm View Post
    But in my document I have other columns, not just que date columns. Can I still use it?


    I downloaded your sample but I could not reproduce it...
    You need to understand that it's impossible to use $ {} variable to rename the columns in Row denormaliser (fieldName) but it's possible for key-value.
    Then I used the setName method for rename column. with step "Rename field Name".
    I used date on exemple, but you can use it the way you need it

    You need to understand a bit of programming logic to add the fields you need
    Because the steps flow will be the same

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

    Default

    There's a solution to this kind of problem without scripting, too: Metadata injection.
    Attached Files Attached Files
    So long, and thanks for all the fish.

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.