Continue transformation flow even if Table Input has no rows

[Gosforth]

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...

[Sparkles]

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

[Gosforth]

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.

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

[gutlez]

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.

[Bizcubed]

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

[Gosforth]

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.

[Gosforth]

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...

[Ana GH]

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?

[Sparkles]

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.

[gutlez]

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.