Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Dynamic comma separated string concatenation

  1. #1

    Default Dynamic comma separated string concatenation

    Hi all,

    I've reached a point in my ETL design where I select columns "Code" and "Value", dynamically generated throughout the process, that have the following example values.

    Code : a1,a2,a3,a4
    Values: title,address,zip_code,city

    I need to concatenate this two columns so the process dynamically creates a datawarehouse table.

    The SQL query resulting from these columns shall be ( ALTER TABLE factSomething ADD ( a1 as title type, a2 as address type, a3 as zip_code type, a4 as city type) ; )

    How can I make this comma separated dynamic merge?

    Thank you all in advance.

    --jr , Portugal

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

    Default

    Hi.
    Maybe you can use "Calculator" Step or "Modified Java Script Value" step.

    Regards,
    Mick

  3. #3

    Default

    Hi Mick.

    Thank you for your reply.

    I've tried with but, expectedly, the outcome of both transformations is a1,a2,a3,a4,title,address,zip_code,city and I want/need to pair them.

    Any thoughts ?

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

    Default A JavaScript Free(tm) Solution

    code-values sample.ktr

    Just to state the obvious, this is then solved as follows with a "Denormalizer" step:

    code-values sample.ktr
    Last edited by MattCasters; 02-21-2011 at 04:49 PM.

  5. #5

    Default

    MattCasters,

    Thank you for helping me and therefore all the community.

    I appreciate your kind gesture.

    Regards,

    --jr

  6. #6

    Default

    Quote Originally Posted by MattCasters View Post
    code-values sample.ktr

    Just to state the obvious, this is then solved as follows with a "Denormalizer" step:

    code-values sample.ktr
    Hi again Matt,

    I have achieved, with your help, this data structure:



    However, this step is pre-javascript where I craft a sql_query to be executed.

    So, I want to do (for this ID 43255 - that has 3 attributes - other IDS have different number of attributes, so this is variable/dynamic) something which the output sounded like:

    Select attribute_1 as Titulo, attribute_2 as Empresa, attribute_3 as Identificador_Empresa, from table_SurveyID where UserTokenID='312asd2342'.

    With this structure I get three rows for each token, in the final DB, such as:

    ID | SurveyID | TOKEN | ATT_1 | ATT_2 | ATT_3
    1 43225 asda 1 NULL NULL
    2 43225 asda NULL 2 NULL
    3 43225 asda NULL NULL 3

    And I want and need it to be dynamically like :

    ID | SurveyID | TOKEN | ATT_1 | ATT_2 | ATT_3
    1 43225 asda 1 2 3

    Any thoughts?

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

    Default

    Use the Denormalizer step!

    Your group fields are SurveyID and Token.
    Your Key field is Codes
    Then you can define fields for Attr_1, Attr_2, etc.
    **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

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.