To transform data and carriage return
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 25
- Joined: Wed May 02, 2007 1:07 am
To transform data and carriage return
Hi,
I am facing an issue - can you please help.
KEY DESC DATE
123 AAA 1/05/09
123 BBB 5/24/11
123 CCC 2/22/12
452 AA 5/2/04
452 BBB 3/6/06
631 AAAA 5/6/10
I have sorted the data on KEY and obtained as below
123 AAA|1/05/09,BBB|5/24/11,CCC|2/22/12
452 AA|5/2/04,BBB|3/6/06
631 AAAA|5/6/10
I am struck after this...
The Actual Output that I am trying to obtain is as below
KEY DESC
123 AAA "on" 1/05/09[NewLine]BBB "on" 5/24/11[NewLine]CCC "on" 2/22/12
452 AA "on" 5/2/04[NewLine]BBB "on" 3/6/06
631 AAAA "on" 5/6/10
Can you please share your thoughts on the above.
Regards,
Kiran Kumar
I am facing an issue - can you please help.
KEY DESC DATE
123 AAA 1/05/09
123 BBB 5/24/11
123 CCC 2/22/12
452 AA 5/2/04
452 BBB 3/6/06
631 AAAA 5/6/10
I have sorted the data on KEY and obtained as below
123 AAA|1/05/09,BBB|5/24/11,CCC|2/22/12
452 AA|5/2/04,BBB|3/6/06
631 AAAA|5/6/10
I am struck after this...
The Actual Output that I am trying to obtain is as below
KEY DESC
123 AAA "on" 1/05/09[NewLine]BBB "on" 5/24/11[NewLine]CCC "on" 2/22/12
452 AA "on" 5/2/04[NewLine]BBB "on" 3/6/06
631 AAAA "on" 5/6/10
Can you please share your thoughts on the above.
Regards,
Kiran Kumar
-
- Participant
- Posts: 91
- Joined: Wed Apr 20, 2005 7:59 pm
- Location: U.S.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 25
- Joined: Wed May 02, 2007 1:07 am
-
- Participant
- Posts: 91
- Joined: Wed Apr 20, 2005 7:59 pm
- Location: U.S.
What you can do is create one more stage variable and handle only the dates from your input record for the another column. It will be as follows:
Input rec:
KEY DESC DATE
123 AAA 1/05/09
123 BBB 5/24/11
123 CCC 2/22/12
452 AA 5/2/04
452 BBB 3/6/06
631 AAAA 5/6/10
Output:
Key DESC ANOTHER COLUMN
123 AAA|1/05/09,BBB|5/24/11,CCC|2/22/12 1/05/09,5/24/11,2/22/12
452 AA|5/2/04,BBB|3/6/06 5/2/04,3/6/06
631 AAAA|5/6/10 5/6/10
Formatted Output:
Key DESC ANOTHER COLUMN
123 AAA "on" 1/05/09[NewLine]BBB "on" 5/24/11[NewLine]CCC "on" 2/22/12 1/05/09 - 5/24/11 - 2/22/12
452 AA "on" 5/2/04[NewLine]BBB "on" 3/6/06 5/2/04 - 3/6/06
631 AAAA "on" 5/6/10 5/6/10
Input rec:
KEY DESC DATE
123 AAA 1/05/09
123 BBB 5/24/11
123 CCC 2/22/12
452 AA 5/2/04
452 BBB 3/6/06
631 AAAA 5/6/10
Output:
Key DESC ANOTHER COLUMN
123 AAA|1/05/09,BBB|5/24/11,CCC|2/22/12 1/05/09,5/24/11,2/22/12
452 AA|5/2/04,BBB|3/6/06 5/2/04,3/6/06
631 AAAA|5/6/10 5/6/10
Formatted Output:
Key DESC ANOTHER COLUMN
123 AAA "on" 1/05/09[NewLine]BBB "on" 5/24/11[NewLine]CCC "on" 2/22/12 1/05/09 - 5/24/11 - 2/22/12
452 AA "on" 5/2/04[NewLine]BBB "on" 3/6/06 5/2/04 - 3/6/06
631 AAAA "on" 5/6/10 5/6/10
-
- Participant
- Posts: 25
- Joined: Wed May 02, 2007 1:07 am
Craig,chulett wrote:Inline Field() functions, perhaps. First use "," as the delimiter and then take the second "|" delimited field from the result. ...
I tried this option as - Field(Result,'|',2).
This is returning "5/24/11,CCC"
I think this is wrong. Per the need it should have returned only 5/24/11.
Let me know your thoughts.
However, if i filter out based on the "," first for 2nd comma, then I would be getting something like this - BBB|5/24/11
Then I should be deriving 5/24/11 out of it.
Correct me I am wrong anywhere.
Craig's suggestion did involve using the Field() function twice, once for the ',' delimiter and once for the '|' delimiter. The order--'|' then ',' or ',' then '|'--is not as important as knowing what the results of each call to the function will be and coding appropriately to obtain the desired result in the end.
You're on the right track...have you tried what you mentioned in your question to see if that is the result you get?
Regards,
You're on the right track...have you tried what you mentioned in your question to see if that is the result you get?
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 25
- Joined: Wed May 02, 2007 1:07 am
-
- Participant
- Posts: 25
- Joined: Wed May 02, 2007 1:07 am
Field(string,",",field_number) to pull fields delimited with commas
Field(string,"|",field_number) to pull fields delimited with pipes
To me, it looks like the DESC/DATE pairs are delimited by the commas, then the DESC and DATE within a pair are delimited by pipes. So, to get the date from a particular pair within the record:
The inner Field() call returns the pair--such as BBB|5/24/11--then the outer Field() call returns the date from that pair--5/24/11. You could also do this with one or more stage variables if you don't want to nest the Field() calls. If you're using IS 8.5 or 8.7, you can loop through each record based upon the number of DESC/DATE pairs (count of pipes perhaps?)
Regards,
Field(string,"|",field_number) to pull fields delimited with pipes
To me, it looks like the DESC/DATE pairs are delimited by the commas, then the DESC and DATE within a pair are delimited by pipes. So, to get the date from a particular pair within the record:
Code: Select all
Field(Field(record,",",pair_number),"|",2)
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 25
- Joined: Wed May 02, 2007 1:07 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 25
- Joined: Wed May 02, 2007 1:07 am
Did you mean
?
Read the <a href="http://publib.boulder.ibm.com/infocente ... ntation</a> for Field() if you haven't yet. Perhaps the struggle would've been much shorter. The examples in the doc are very straightforward.
Regards,
Code: Select all
Field(StageVar3,'|',1)
Read the <a href="http://publib.boulder.ibm.com/infocente ... ntation</a> for Field() if you haven't yet. Perhaps the struggle would've been much shorter. The examples in the doc are very straightforward.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.