Datastage - Maximum length of a column

Palci · Post by **Palci** » Thu Nov 17, 2011 11:08 am

Hi,

I have a table with the following data
--------------------------------------------------------------------------------------
Dialed Digit | MCC |MNC
+46 | 240 |AA
+46675 | 240 |02
+467230 | 240 |02
+46252 | 240 |07
0 | 240 |07
-------------------------------------------------------------------------------------
I need two outputs:
Max_Dialed_Digit = Maximum length of the column dialed digit i.e. Len(+467230) = 7
Min_Dialed_Digit = Minimum length of the column dialed digit i.e. Len(0) = 1
How do I achieve this is datastage? Please help.

pandeesh · Post by **pandeesh** » Thu Nov 17, 2011 11:17 am

Just add one more column in transformer stage which contains the len(dialled_string).
Then use Aggregator stage and find min,max values.

Palci · Post by **Palci** » Thu Nov 17, 2011 10:48 pm

pandeesh wrote:Just add one more column in transformer stage which contains the len(dialled_string).
Then use Aggregator stage and find min,max values.

Thanks Pandeesh, I will try this one in my job.. Just a thought is there a way to do this in transformer stage??

Also based on these minimum and maximum values I want to write a loop condition in my job for browsing the data in my table. How can I implement that using the "loop while" in the transformer stage in datastage 8.5??

pandeesh · Post by **pandeesh** » Fri Nov 18, 2011 1:17 am

I am not sure whether we can implement that using transformer alone.
i think we can sort the data in ascending based on length so that the min value will come in the first row and max wil ocme as the last record.
Then you can pick the min,max values.
I am not sure about the looping what you mean.
Please provide your sample input and the output you are expecting.
So that, someone will help you here.

Thanks

ray.wurlod · Post by **ray.wurlod** » Fri Nov 18, 2011 2:47 am

It can easily be done in the Transformer stage.
Simply calculate the length in the current record and compare with the length in the previous record. If it's smaller, update the min. If it's larger, update the max. Output at end of group.

ShaneMuir · Post by **ShaneMuir** » Fri Nov 18, 2011 3:11 am

You would probably have to do any calculations on a single node for such a requirement, else wouldn't you get a different measure for each node?

Palci · Post by **Palci** » Fri Nov 18, 2011 4:37 am

ray.wurlod wrote:It can easily be done in the Transformer stage.
Simply calculate the length in the current record and compare with the length in the previous record. If it's smaller, update the min. If it's larger ...

This is how I was planning to do it, But how can I identify the 1st row and the 2nd row and so on in datastage??

ShaneMuir · Post by **ShaneMuir** » Fri Nov 18, 2011 5:13 am

A record in a stage variable are processed sequentially per node. So if you have one node then it will process the first row, then the second row etc in which ever order the rows are sorted.

So if you have several stage variables these are also processed sequentially. Ie the 1st stage variable is processed before the 2nd stage variable and so on. So if you set the 1st variable to be the length (current value), then the 2nd variable (previous value) to check against the current value to see if its bigger or smaller and set it accordingly.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 18, 2011 2:17 pm

You *could* identify them with @INROWNUM but you're not really interested in the row number - all you're interested in is "this row" and "the previous row".