Datastage - Maximum length of a column
Moderators: chulett, rschirm, roy
Datastage - Maximum length of a column
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.
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 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??
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
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
pandeeswaran
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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??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 ...
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: