Hi ,
I have a following scenario.
Input:
ID|NAME|DT
100|AAA| 2.0;300.90;09/23/2009;12/20/2007;00.65;24;04/10/2008;0;
101|BBB|04/22/2005;30;78
102|CCC|0;24.50;90;
104|DDD|45;11/02/2009;90;03/23/2009
Required output:
ID|NAME|DT
100|AAA|09/23/2009;12/20/2007;04/10/2008
101|BBB|04/22/2005
102|CCC|
104|DDD|11/02/2009;03/23/2009
We need only the date values in DT field and want to trim rest.
Could you please share your thought how to derive in datastage.
Thanks in advance.
Prem
Column Derivation
Moderators: chulett, rschirm, roy
-
parameswar
- Premium Member
- Posts: 66
- Joined: Sun Apr 08, 2007 12:34 pm
Re: Column Derivation
Hi,parameswar wrote:Hi ,
I have a following scenario.
Input:
ID|NAME|DT
100|AAA| 2.0;300.90;09/23/2009;12/20/2007;00.65;24;04/10/2008;0;
101|BBB|04/22/2005;30;78
102|CCC|0;24.50;90;
104|DDD|45;11/02/2009;90;03/23/2009
Required output:
ID|NAME|DT
100|AAA|09/23/2009;12/20/2007;04/10/2008
101|BBB|04/22/2005
102|CCC|
104|DDD|11/02/2009;03/23/2009
We need only the date values in DT field and want to trim rest.
Could you please share your thought how to derive in datastage.
Thanks in advance.
Prem
Looking for your thoughts to resolve the issue in datastage without any basic routine. Is it only PX routine to solve the issue or is there any other way we can handle it in datastage .
Thanks,
Prem
-
priyadarshikunal
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
It can be done through datastage too, but may be a bit messy.
you can pivot the record based on delimiter then do a vertical pivot using stage variables in ignore the values which are not valid using isvalid funtion.
you can pivot the record based on delimiter then do a vertical pivot using stage variables in ignore the values which are not valid using isvalid funtion.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
Sainath.Srinivasan
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Is the number of entries small ? something like 20 !?
If it is small, you can use stage variables to extract each entry and concatenate them for your output.
If it is large, you can pivot as PriyadharshiKunal suggested and "vertical pivot back". But the main stream must be from your existing source and do an "outer join" as otherwise you may loose your "CCC" entry.
You can also
1.) Prefix and append a semicolon to the last column
2.) replace any non-";nn/nn/nnnn;" patterns to semicolon using Unix commands
3.) trim all semicolons
If it is small, you can use stage variables to extract each entry and concatenate them for your output.
If it is large, you can pivot as PriyadharshiKunal suggested and "vertical pivot back". But the main stream must be from your existing source and do an "outer join" as otherwise you may loose your "CCC" entry.
You can also
1.) Prefix and append a semicolon to the last column
2.) replace any non-";nn/nn/nnnn;" patterns to semicolon using Unix commands
3.) trim all semicolons