Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Carte: Passing variable/parameter to use for filtering

  1. #1
    Join Date
    Dec 2009
    Posts
    609

    Default Carte: Passing variable/parameter to use for filtering

    Hi guys,

    I got one question:
    Using Carte I would like to pass a parameter/variable into a given KTR to filter excel-rows according to the value of this parameter.

    While it is rather obvious to use such parameter/variable inside a "Table Input" step I could not figure an approach for applying such
    filter-logic to get only specific rows from an Excel-File.

    Does somebody have a good/usable idea/input on that?

    Thx and best regards,

    Tom

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

    Default

    With a Table Input step you pass a filter expression to a query analyzer.
    Excel is just a file format, there is no engine you could delegate filtering to.
    Of course, filtering could be implemented as part of the step, but I rather use a separate filtering step.
    A Java Filter step would be ideal, if not a vital line of code was missing from JavaFilter.java:

    Code:
    // without substitution no variables allowed in expression
    
    meta.setCondition(environmentSubstitute(meta.getCondition()));
    
    // find next lines in source
    
    }
    
    
    if (log.isRowLevel()) logRowlevel("Read row #"+getLinesRead()+" : "+getInputRowMeta().getString(r));
    
    
    boolean keep = calcFields(getInputRowMeta(), r);
    Last edited by marabu; 06-16-2013 at 01:19 PM. Reason: better place for code insertion
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Dec 2009
    Posts
    609

    Default

    Hi marabu,

    thx for the feedback.
    Using the "Filter Step" seems not to be working as this step won't accept parameters/variables.
    Then I tried "Java Filter".
    Howver, neither this expression CID.equals("${CIDToLoad}") nor CID.equals(${CIDToLoad}) won't work (causing such errors in STDOUT:

    INFO 16-06 18:51:45,515 - Dummy (do nothing) 2 - Finished processing (I=0, O=0, R=121, W=121, U=0, E=0)
    ERROR 16-06 18:51:45,517 - Java Filter - Unexpected error
    ERROR 16-06 18:51:45,518 - Java Filter - org.pentaho.di.core.exception.KettleValueException:
    java.lang.reflect.InvocationTargetException
    at java.lang.Thread.run (null:-1)
    at org.pentaho.di.trans.step.RunThread.run (RunThread.java:50)
    at org.pentaho.di.trans.steps.javafilter.JavaFilter.processRow (JavaFilter.java:106)
    at org.pentaho.di.trans.steps.javafilter.JavaFilter.calcFields (JavaFilter.java:197)
    at org.codehaus.janino.ScriptEvaluator.evaluate (ScriptEvaluator.java:46

    "CID" being a String-column coming from the Excel-File and filled always.
    ${CIDToLoad} being the parameter/variable coming via URL to the KTR (in my spoon I configured it with a default value for testing purposes)

    Do you maybe have another idea?

    Cheers,

    Tom

    P.S. Using PDI 4.4.0

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

    Default

    Maybe I wasn't bold enough: The Java Filter step sports a dollar sign near the condition field, but lacks substitution code.
    You simply can't use textual substitution via ${CODToLoad}, before you compile the patch I suggested.
    As a workaround you can use a Get Variables step to add your parameter value to the row just before a Filter Rows step.
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Dec 2009
    Posts
    609

    Default

    Hi marabu,

    thx again. "Get Variables" indeed works good enough.
    So now my KTR looks like this:
    "Get variables" (access the URL-Parameter)
    "Read Excel"

    Both are sources. Then I combine them by using "Join Rows (Cartesian Product)" and then using "Filter rows" (with a simple equal-condition) to get the matching rows.

    Task solved, thx for pointing me to the correct direction

    Cheers,

    Tom

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

    Default

    Quote Originally Posted by marabu View Post
    As a workaround you can use a Get Variables step to add your parameter value to the row just before a Filter Rows step.
    No need for a cartesian join, if you do it this way.
    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.