Output Record count in a transformer

ady · Post by **ady** » Wed Dec 06, 2006 11:03 am

I am choosing some rows from a column in the transformer with a constraint and writing the output to a sequential file.

The constraint is l_to_Tform.MG_NO [1,1] = "#" ie: when a row starts with #... reject it.

I need to get the output row count. Is there a way to do that in the same transformer ?

ArndW · Post by **ArndW** » Wed Dec 06, 2006 11:07 am

You should be using "<>" instead of "=" to reject those records. You do have a stage mnemonic called @OUTROWNUM that you can use in the transform.

ady · Post by **ady** » Wed Dec 06, 2006 1:05 pm

The constraint was a TYPO. i am using "<>"

I just need the highest value of the row count . ie: if there are 12345667 rows in teh column then the value returned should be "12345667". How can i use @OUTROWNUM to get this ?

ArndW · Post by **ArndW** » Wed Dec 06, 2006 1:37 pm

beaditya - let's take a step back. Where do you want this row value returned? A transformer gets executed for each incoming row. You could do an after-stage subroutine call and pass it the value of @OUTROWNUM. But all this depends what you want to do with the maximum row number.

DSguru2B · Post by **DSguru2B** » Wed Dec 06, 2006 1:45 pm

Add an two extra columns to your output. One with derivation @OUTROWNUM and specify any dummy key on the second. Let that go down to the aggregator, group on the dummy key and choose max or last for the column which had derivation of @OUTROWNUM.
Also, if you want all rows that start with '#', dont you want '=' in the constraint instead of '<>'

baglasumit21 · Post by **baglasumit21** » Thu Dec 07, 2006 3:31 am

beaditya wrote:I am choosing some rows from a column in the transformer with a constraint and writing the output to a sequential file.

The constraint is l_to_Tform.MG_NO [1,1] = "#" ie: when a row starts with #... reject it.

I need to get the output row count. Is there a way to do that in the same transformer ?

Add a dummy column of type integer to the output link with a derivation as 1 and then introduce a aggregator after the transformer that will give you the sum of the dummy column. this sum will be the row count

ArndW · Post by **ArndW** » Thu Dec 07, 2006 3:56 am

Using the aggregator stage in this case will not really be necessary (or efficient). The method will depend on what the original poster needs this for. If this number of rows is needed from another job or sequence, then a call to DSGetLinkInfo() will get that value. But that method doesn't make sense if the value is needed in a subsequent stage in the same job. So we need to wait for an explanation and then come up with a simple and functional solution.

ady · Post by **ady** » Thu Dec 07, 2006 2:54 pm

I am using this to get the link row count

DSGetLinkInfo (DSJ.ME, "Transformer_56", "l_to_sq", DSJ.LINKROWCOUNT)

But in one job it gives the output as -9. I really dont get why it happens ?

DSguru2B · Post by **DSguru2B** » Thu Dec 07, 2006 3:22 pm

You cannot do that while the link is open and data is being processed. You need to do that in the after job routine or something.

thebird · Post by **thebird** » Thu Dec 07, 2006 7:17 pm

You would need to give more input regarding your requirement as Arnd has said - so that someone here can actually help out ..

But it seems like you are trying to get the output row count from a Transformer. If so why not just set @OUTROWNUM in the Transformer and find the MAX() in an aggregator stage?

aakashahuja · Post by **aakashahuja** » Fri Dec 08, 2006 3:29 am

Or you can use a Stage Variable (starting friom 1 and incremented by 1 every time) and then you can check if the input link is finished then you can pass the value of that stage variable to the output whereever required.

chulett · Post by **chulett** » Fri Dec 08, 2006 7:54 am

Not sure why you would do that when it is exactly the same as the provided System Variable @OUTROWNUM.

As noted, we'd really need to know the 'why' behind the 'what' - why are they doing this and what do they plan doing with the number? That knowledge will help get them a proper answer.

ady · Post by **ady** » Fri Dec 08, 2006 8:49 am

@Chulett

@OUTROWNUM gives all the row numbers as a seperate column, but I just need the total number of rows in a column. Then again I have to plugin a AGGREGATOR to take the last value of that column ( and this does not work with the constraints I am giving).

@DSguru2B

You cannot do that while the link is open and data is being processed. You need to do that in the after job routine or something.

I have this problem with one job where the transformer I have given DSGetLinkInfo (DSJ.ME, "Transformer_56", "l_to_sq", DSJ.LINKROWCOUNT) is run before the stage "Transformer_56" or the link "l_to_sq".

So it does give the value. Can I in anyway delay the the execution of this command so that the Transfor_56 is executed first and i can get the row count ?

chulett · Post by **chulett** » Fri Dec 08, 2006 9:28 am

beaditya wrote:@OUTROWNUM gives all the row numbers as a seperate column, but I just need the total number of rows in a column. Then again I have to plugin a AGGREGATOR to take the last value of that column ( and this does not work with the constraints I am giving).

I'm sorry, but this makes absolutely no sense to me. I don't see any difference between what you say @OUTROWNUM 'gives' and what you 'just need'. And I use Aggregators to do stuff like this 'all the time'.

I guess I'll just back away and let others dance this dance...

ady · Post by **ady** » Fri Dec 08, 2006 10:24 am

@Chulett

Sorry for the confusion but...

As far as my knowledge goes when we give @ROWNUM in the stage variables and map it to a column we get the output values in that column as 1,2,3,4,5,6,7,8,9.........14567. ( The number of that particular row )

I just need the value 14567 ( the number of the last row).

When I use an aggregator to count the rows I give Count(IN_Row) as the derivation (this works pretty well for me) but I cannot give a aggregator in my current job because then I have a problem of columns coming from various stages ( I have to write them together in a Oracle table, so i'll have a new problem to join them ) .... and thats why I am using DSGetLinkInfo (DSJ.ME, "Transformer_56", "l_to_sq", DSJ.LINKROWCOUNT).

Hope I could communicate well this time ...

DSXchange

Output Record count in a transformer

Output Record count in a transformer

Re: Output Record count in a transformer