DSXchange

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

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

Hi,

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

It can be done in unix scripting .Try with AWK

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.

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