DSXchange

Hi,

Ca any body tell me how to count the number of records in a transformer.

For example I am sending 100 rows into the transformer and I want the output to be 1 row only having the count as 100.

Thanks

Why transformer stage? Much easier when you use the stage that is designed for the task (e.g. Aggregator Stage).

Mike

The Transformer stage cannot be used for your intended purpose, because there is no way to detect that the last row is being read. As mentioned before, an Aggregator stage will do that.

Yest thats right you can use an aggregator stage to get the row count.

use the following options:

in the aggregator stage:
aggregation type: CountRows.

this should work.

[quote="Mike"]Why transformer stage? Much easier when you use the sta

yes we can do that in aggregator stage, but if I use some functions already in the transformer and I want to implement this in the same transformer and dont want to add an extra stage.

Can we handle this in transformer???

For the second time in this thread, it cannot be done in a transformer becuase you never know when the last row has arrived.

ArndW wrote:For the second time in this thread, it cannot be done in a transformer becuase you never know when the last row has arrived.

K thank you for your response

And don't worry about extra stages. That's what pipeline parallelism is all about... and with operator combination, multiple stages may be combined into a single process anyway.

Mike

[quote="Mike"]And don't worry about extra stages. That's what pipeline parallelism is all about...

And just for knowledge I am asking cant we count the number of rows in atranformer?? I thought we can do any transformations or process in the transformer.

Thanks

Yes, you can count in the transformer, you just won't know the total count until you run it through the aggregator... so effectively pointless

kittu.raja wrote:

Mike wrote:And don't worry about extra stages. That's what pipeline parallelism is all about...

And just for knowledge I am asking cant we count the number of rows in atranformer?? I thought we can do any transformations or process in the transformer.

Thanks

Yes. We can count the number of rows in transformer but you will get 100 rows with counts as 1 to 100.
The only key point here is your requirement to get only 1 row with count as 100. That is where the problem is.
The only way to achieve this is through transformer constraint. But to derive this constraint we need to know the row count before the transformer either through aggregator or the count should be static which is not in your case.

I don't know why I am writing this
but I think its just to let you know how can you do that (is it for an interview?)

you need two transformers running in sequential mode

1. Using stage variable count the number of records and put it in a derived column say Count or use @OUTROWNUM
2.sort the data before second transformer using link sort on Count.
3. put a constraint in second transformer to release only one record(first) and pass only one column called count.

I don't think any one will use this technique except explaining it in an interview as this will create a slow running job ignoring the advantages of PX.