Splitting the coulmn in a record

[paranoid]

Hi,

I am having some data as follows:

Eg:( for one record)
5
3
4
5;5;6;7
8
9
10

My ouput should be as follows:

5
3
4
5
5
6
7
8
9
10

What is the best way of achieving this output?

I have started looking at data split stage from DS manual.. Is there any other way of doing this?

Appreciate your help

Thanks
Sue

[chulett]

Write them out with linefeeds in place of the semi-colons so they end up as separate records.

[paranoid]

Hi,

Thanks for the quick reply.

Could you please provide me an example?

Thanks again

Sue

[chulett]

Code: Select all

Convert(";",CHAR(10),YourField)

That will write out "5;5;6;7" as four records at once since CHAR(10) is a LineFeed a.ka. UNIX record delimiter.

[ray.wurlod]

Or, since you're on Windows, use Ereplace() to replace each";" with carriage return and line feed.

Code: Select all

Ereplace(InLink.TheString, ";", Char(13):Char(10), -1, 0)

[chulett]

Windows? I could have sworn when I looked at it before replying it said UNIX. Trickery!

Yes, of course, Windows will need the pair of CR/LF characters as the record delimiter.

[paranoid]

Hi,

Could you please explain me how it works when i need to load that data into a table?

If i include the line feeds, DataStage Job is taking the data next to ";" as a new record record and aborting.

Thanks

Sue

[chulett]

Write it to a file as noted, then read it back before loading to a table. All in one job. In your example, you would write 7 'records' but read back 10.

[paranoid]

Hi,

The job flow is as follows:

Input ---> Transformer ---> File---> Table

In Transformer, Suppose if a column address needs to be divided into address1 and address2, the derivation for that column(address) is given as Ereplace(FromProgressICSP.addr, ";", Char(13):Char(10), -1).

Please help me how to populate address1 and address2 using the above derivation.

Thanks again for all the help!!

Sue

[chulett]

How can we know? Give us an example (a real example) of this address data and what your business rule to 'split' it is.

[ray.wurlod]

Hi,

The job flow is as follows:

Input ---> Transformer ---> File---> Table

In Transformer, Suppose if a column address needs to be divided into address1 and address2, the derivation for that column(address) is given as Ereplace(FromProgressICSP.addr, ";", Char(13):Char(10), -1).

Please help me how to populate address1 and address2 using the above derivation.

Thanks again for all the help!!

Sue

That's a totally different question, and would be resolved with the Field() function - you are now after separate columns rather than separate rows. This question really deserved a new thread.

[paranoid]

Hi,

I have data as follows:


Input record:
---------------
CompanyNumber:11
Name:XXXX
Address: 111 STREET NAME;P O BOX 1111
Code:0;0;41;0
City:XXXX
State:XXXX

I need to split the data to:

Output Record:
-----------------
CompanyNumber:11
Name:XXXX
Address1: 111 STREET NAME
Address2:P O BOX 1111
Code1:0
Code2:0
Code3:41
Code4:0
City:XXXX
State:XXX


Thanks

Sue

[chulett]

As noted, a completely different question. Next time, new topic please. And as noted, the Field() function can be used to parse a delimited string into component pieces, and your address and code fields are ';' delimited strings. Check the online help.

[paranoid]

Hi,

Thank you very much. Field function solved my problem.

Sue :D

[tennetiharika]

U shud be able to do it even using pivot stage in DS