To transform data and carriage return

kiran kumar · Post by **kiran kumar** » Wed May 23, 2012 9:56 pm

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

mobashshar · Post by **mobashshar** » Wed May 23, 2012 11:04 pm

Change | to " on " and change , to \n or CHAR(10) for New Line

ray.wurlod · Post by **ray.wurlod** » Thu May 24, 2012 5:37 am

Look for the pxEreplace() function on DSXchange. This will allow you to change "|" to " on " with a single function call.

kiran kumar · Post by **kiran kumar** » Thu May 24, 2012 11:04 am

Thanks, Ray & Mobashshar

It worked...
However, can you please suggest how to read just the date value from the entire record "AAA|1/05/09,BBB|5/24/11,CCC|2/22/12"

Like just the 1/5/09 - 5/24/11 - 2/22/12 from the entire string.
The required output is used in another column.

chulett · Post by **chulett** » Thu May 24, 2012 11:24 am

Inline Field() functions, perhaps. First use "," as the delimiter and then take the second "|" delimited field from the result.

mobashshar · Post by **mobashshar** » Thu May 24, 2012 11:47 am

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

kiran kumar · Post by **kiran kumar** » Thu May 24, 2012 12:54 pm

chulett wrote:Inline Field() functions, perhaps. First use "," as the delimiter and then take the second "|" delimited field from the result. ...

Craig,

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.

jwiles · Post by **jwiles** » Thu May 24, 2012 1:23 pm

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,

kiran kumar · Post by **kiran kumar** » Thu May 24, 2012 1:49 pm

Yes, James.

I'm trying to figure it out. Its failing all the time

kiran kumar · Post by **kiran kumar** » Thu May 24, 2012 3:13 pm

In general, is there a way to read the value before a comma and after a comma separately?

jwiles · Post by **jwiles** » Thu May 24, 2012 3:42 pm

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:

Code: Select all

Field(Field(record,",",pair_number),"|",2)

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,

kiran kumar · Post by **kiran kumar** » Thu May 24, 2012 4:17 pm

Thanks, James.

This is working.
I'm trying to find how can we read just the string "BBB" out of it.

Any help!

ray.wurlod · Post by **ray.wurlod** » Thu May 24, 2012 4:33 pm

The logic is very similar. Play with the delimiter character and occurrence value arguments.

kiran kumar · Post by **kiran kumar** » Thu May 24, 2012 4:50 pm

Hi Ray,

Thank You!

It did work...after a long struggle.
I used: Field(StageVar3,'|',2,1) which derived me BBB.

Now, I see how can i get the desired output. Will keep you all posted on the final result.

Thanks.
Kiran Kumar

jwiles · Post by **jwiles** » Thu May 24, 2012 5:55 pm

Did you mean

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,