Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: parameter values from another parameter

  1. #1
    Join Date
    Oct 2015
    Posts
    6

    Default parameter values from another parameter

    Hi,

    I need to use in a parameter the values I get from another parameter.
    For example in the database I have 2 columns:
    - country
    - cities. In this column the cities are all in a cell separated with comma
    So I have a parameter for choosing the country and in this parameter as a Post-Processing formula I have:
    =CSVARRAY([Countries];0;","; )

    Now I don't now how to choose in parameter Cities the datasource to be the parameter ${Country} (which contains only the cities from that country).
    Is this possbile?

    Thank you for your suggestions.
    Last edited by AndreiMiron; 05-25-2017 at 03:42 AM.

  2. #2
    Join Date
    Nov 2009
    Posts
    688

    Default

    The query for selecting the country something like
    Select distinct(country) from table
    This wil populate the parameter ${Country}

    Then a new query
    Select distinct(cities) from table where country = ${Country}
    This query you can use to select a city parameter

  3. #3
    Join Date
    Oct 2015
    Posts
    6

    Default

    Hi Johan,

    I tried that. It is not working because the table is like this:
    Germany | Berlin, Koln, Munchen
    UK | London, Manchester, Liverpool

    What I want is after the user is choosing the Country to have to choose some cities from the corresponding list.
    If i do like you suggest I cannot have a Multi Value List because the query is returning a string and not some rows. So I have to convert the string to array in order to use it as values in parameter.
    I think I did it using a post-processing formula. Unfortunately in the parameter for choosing the City (from the list transformed in array) I don't now how to put the array already processed.

  4. #4
    Join Date
    May 2016
    Posts
    282

    Default

    The answer will be different depending on your database. For example, if you use an Oracle Database you can use the REGEXP_SUBSTR function. I don't know about similar functions for another databases, google them. Or create a table or view on your database to perform it.
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  5. #5
    Join Date
    Oct 2015
    Posts
    6

    Default

    Quote Originally Posted by Ana GH View Post
    The answer will be different depending on your database. For example, if you use an Oracle Database you can use the REGEXP_SUBSTR function. I don't know about similar functions for another databases, google them. Or create a table or view on your database to perform it.
    Regards
    Hi Ana,

    I'm using MySQL not ORACLE but regexp exist in it. I don't understand how this function can help me. Can you elaborate please?

  6. #6
    Join Date
    Oct 2015
    Posts
    6

    Default

    For clarification I added 2 Excel files similar with my tables.
    Country City
    Germany Munchen,Koln,Berlin
    UK London,Manchester,Liverpool

    And Cities table:
    City Sales
    Munchen 1
    London 2
    Koln 3
    Berlin 4
    Manchester 5
    Liverpool 6
    Liverpool 7
    Manchester 12
    Koln 43
    Berlin 41
    Munchen 21


    So in the first parameter the user may select the country and in the second parameter the user may select the city or cities available in that Country.

  7. #7
    Join Date
    May 2016
    Posts
    282

    Default

    I don't work with MSSQL, you'll have to read the documentation, google for your answer or ask on the proper MSSQL forums.
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  8. #8
    Join Date
    Aug 2016
    Posts
    142

    Default

    I think Ana is suggesting that you can use SQL to explode the string as opposed to a post processing formula. Unfortunately MySQL does not have an equivalent REGEXP_SUBSTR function.

    Here are some resources that will help to emulate it though

    https://stackoverflow.com/questions/...-separated-row

    http://www.marcogoncalves.com/2011/0...ing-into-rows/

    However you mentioned that you have successfully done this with a post processing formula but cannot select the individual cities. Can you upload an example file that demonstrates your issue? I am having trouble visualising the problem

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.