Hi,
I have a Table which contains data in this format:
ID CITIES
12 Lahore,Karachi,Islamabad
23 Dubai, Abu Dhabi
45 New York
46 London
I need to separate this data like this:
ID CITIES
12 Lahore
12 Karachi
12 Islamabad
23 Dubai
23 Abu Dhabi
45 New York
46 London
Please suggest some ways in Data stage
Data Stage CSV column seperation to Multiple rows
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 2
- Joined: Thu Apr 09, 2015 5:18 am
Welcome.
Normally we'd start by asking things like 'what have you tried?' and 'what issues have you seen?'. Show us that you've at least tried to solve your puzzle before you came here and just posted input and output. In this case though, it's just a one word answer: pivot.
More specifically: a horizontal pivot of columns to rows. Oddly enough, there's a stage for that.
Normally we'd start by asking things like 'what have you tried?' and 'what issues have you seen?'. Show us that you've at least tried to solve your puzzle before you came here and just posted input and output. In this case though, it's just a one word answer: pivot.
More specifically: a horizontal pivot of columns to rows. Oddly enough, there's a stage for that.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I use to have the same issue, i had that input :
FIELD
---------------
AAA;BBB;CCC;DDD
AAA;BBB;CCC;DDD
And i had to get that output :
FIELD
---------------
AAA
BBB
CCC
DDD
AAA
BBB
CCC
DDD
I do it by using a loop on a transformer (i think it's aviable since DataStage 8.5) :
I create a Stage Variable (CommaNum) with the value :
2 + Count(InputLink.FIELD,";")
My loop condition
@ITERATION < CommaNum
And my output field derivation
Field(InputLink.FIELD,";",@ITERATION)
FIELD
---------------
AAA;BBB;CCC;DDD
AAA;BBB;CCC;DDD
And i had to get that output :
FIELD
---------------
AAA
BBB
CCC
DDD
AAA
BBB
CCC
DDD
I do it by using a loop on a transformer (i think it's aviable since DataStage 8.5) :
I create a Stage Variable (CommaNum) with the value :
2 + Count(InputLink.FIELD,";")
My loop condition
@ITERATION < CommaNum
And my output field derivation
Field(InputLink.FIELD,";",@ITERATION)
Last edited by Thomas.B on Thu Apr 09, 2015 9:44 am, edited 1 time in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: