Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Continue transformation flow even if Table Input has no rows

  1. #1
    Join Date
    Jun 2016
    Posts
    181

    Default Continue transformation flow even if Table Input has no rows

    Hi,

    I struggle with following scenario:
    In transformation I have table input step. If there are some rows then I count them using Memory Group By and make some calculation. But if there are no rows, step Memory Group By will return nothing and Filter Rows will not trigger. I can use Detect Empty stream and it helps to trigger filter. BUT, this step will set ALL fields to null so in next step I have no parameters.
    How to deal with it? I cannot use two transformations and job because I pass through all rows from first step. There is some step that will save parameters at the beginning of the flow to use them in the further part of the transformation?
    Set session and get session works only in two separate transformations?
    I can play with temp table (insert and get) or temp file but this costs a time...

  2. #2
    Join Date
    Aug 2016
    Posts
    289

    Default

    You have a step after Detect empty stream to hardcode what values you want in the dummy "null" row.

  3. #3
    Join Date
    Jun 2016
    Posts
    181

    Default

    I do not get it.
    What step? All fields after Detect Empty stream will be set to null. I need them keep previous values. These parameters are dynamic.

    Quote Originally Posted by Sparkles View Post
    You have a step after Detect empty stream to hardcode what values you want in the dummy "null" row.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Quote Originally Posted by Gosforth View Post
    In transformation I have table input step.... But if there are no rows,
    Then you don't have your parameters in your query.
    They are getting lost at the Table Input Step, not the Detect Empty rows.

    You can do something like :
    Select *, '${Param1}' as Param1 from Table where Field1=${Param1}
    And you will always get at least one row back with your parameter values.

    If you need to do it a different way, you could go Table Input -> Detect Empty Rows -> Get Variables -> Remainder of Transform
    Remember that Parameters are just a special type of variable.

  5. #5
    Join Date
    Jul 2013
    Posts
    2

    Default

    You could try having a "get variables" step to load your parameters and join it with the TableInput step using a MergeJoin step setting the 'get variables' step as the first step, the 'TableInput' step as the second step and having a Join Type of LEFT OUTER. No Key fields needed.

    That way you'll always have at least one row

  6. #6
    Join Date
    Jun 2016
    Posts
    181

    Default

    Quote Originally Posted by Bizcubed View Post
    You could try having a "get variables" step to load your parameters and join it with the TableInput step using a MergeJoin step setting the 'get variables' step as the first step, the 'TableInput' step as the second step and having a Join Type of LEFT OUTER. No Key fields needed.

    That way you'll always have at least one row
    This will not work since one stream will generate nothing if no rows in table.

  7. #7
    Join Date
    Jun 2016
    Posts
    181

    Default

    This is in fact what I'm doing right now to conserve parameters after Input Table step. But if there is no row in table, sql query will return nothing (not even this "Select *, '${Param1}' as Param1").
    That's a pity that Detect Empty Stream clears stream (would be nice to decide what to clear).

    I got another idea. I have to check if clients were buying 6 months ago, 6-24 months ago, if they never bought anything. Taking customer number from current month. So I will create at least two transformations. Each will be independent. Each one will have parameters input at the beginning. Little bit clunky but I think it will work. Maybe marking those records that were processed in first pass will decrease time...

  8. #8
    Join Date
    May 2016
    Posts
    280

    Default

    And wouldn't it work using the "Get variables" step to add columns to the "Detect empty stream" and afterwards use the "Select values" step to use the columns of the "Get variables" step instead of the null values?
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  9. #9
    Join Date
    Aug 2016
    Posts
    289

    Default

    Detect Empty stream doesn't clear anything. It doesn't have anything to clear! It receives nothing, and creates an output based on that. "Nothing" is translated to "null" in Detect Empty Stream. If you want something other than "null", you must hardcode it or otherwise manipulate it after the Detect Empty Stream step.

  10. #10
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Quote Originally Posted by Gosforth View Post
    sql query will return nothing (not even this "Select *, '${Param1}' as Param1").
    That's a pity that Detect Empty Stream clears stream (would be nice to decide what to clear).
    If you structure your SQL correctly, you can set it up so that you always get at least one row back (even if that row has nulls for everything other than your parameters)

    You're right... I forgot that about the Detect Empty Streams step. The way around that is to copy row output from the table input step.
    Table Input -> Transformation Point (1)
    (Copy Output) -> Detect Empty Rows -> Get Variables -> Transformation Point (1)

    Transformation Point (1) is the first step in the rest of your transformation.

    But I still think you're trying to do something odd if you're *expecting* to continue your transform when there's no data coming from your source.

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.