DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
joycerecacho
Participant



Joined: 26 Aug 2008
Posts: 179

Points: 3404

Post Posted: Mon Nov 21, 2011 12:24 pm Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Server
OS: Unix
Hi everybody.

I know this topic is too old but I have a question about it.
In my case, I need the opposite.
I have a string delimited by ',' and I want to split it into rows.

Like:
Field1 Field2
Brazil Joyce, Mary, John
USA Carol, Monica

Result:
Field1 Field2
Brazil Joyce
Brazil Mary
Brazil John
USA Carol
USA Monica

Is it possible?

I´m using the DS 7.5.1 server.

Best Regards,

_________________
Joyce Recacho
Brazil / SP
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 51688
Location: Canberra, Australia
Points: 280710

Post Posted: Mon Nov 21, 2011 2:41 pm Reply with quote    Back to top    

Parse the comma-delimited field into separate columns using Transformer stage then use Pivot stage.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong
currently hiring: Sydney and Melbourne

Last edited by ray.wurlod on Mon Nov 21, 2011 10:38 pm; edited 1 time in total
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 38745
Location: Denver, CO
Points: 197842

Post Posted: Mon Nov 21, 2011 6:36 pm Reply with quote    Back to top    

Split off, linked and moved to the proper forum. Wink

Another possibility - the technique mentioned in this FAQ post.

_________________
-craig

You may ask yourself, "Well, how did I get here?"
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 1460
Location: USA
Points: 10388

Post Posted: Mon Nov 21, 2011 7:16 pm Reply with quote    Back to top    

Craig, is it possible to get the "special access" to read that FAQ link?

_________________
 
Eric
_________________

Time is nature's way of keeping everything from happening at once.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 38745
Location: Denver, CO
Points: 197842

Post Posted: Mon Nov 21, 2011 9:00 pm Reply with quote    Back to top    

Confused

AFAIK, there shouldn't be any "special access" required to read that forum, it's one of the public ones. Is that what it is telling you? Anyone else having an issue?

Eric, can you access it directly from the FAQ forum? It's almost at the bottom of the listed posts there.

_________________
-craig

You may ask yourself, "Well, how did I get here?"
Rate this response:  
Not yet rated
joycerecacho
Participant



Joined: 26 Aug 2008
Posts: 179

Points: 3404

Post Posted: Tue Nov 22, 2011 5:48 am Reply with quote    Back to top    

Hi guys.

Actually the content of the column 'Person' from the example below can have unlimited values separated by comma, they are not established, it is dynamic.

Ex.
Country | Person
BRA | Joyce, Gabriela, Juliana, Thays, Maria, Priscila ;* (6) - could be 73, for example.
USA | Mary, John ;* (2)
CHI | Roberto, Pedro, José ;* (3)
...

I don´t know how many values the register has, so, the solution which uses the transform doesn´t work, right?

Tk u guys.

Best Regards,

_________________
Joyce Recacho
Brazil / SP
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 1460
Location: USA
Points: 10388

Post Posted: Tue Nov 22, 2011 7:41 am Reply with quote    Back to top    

chulett wrote:
Confused

Eric, can you access it directly from the FAQ forum? It's almost at the bottom of the listed posts there.


I get this message when clicking any topic within the FAQ forum. Maybe it's just my ID having a problem.

"Sorry, but only users granted special access can read topics in this forum."

Thanks.

_________________
 
Eric
_________________

Time is nature's way of keeping everything from happening at once.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 38745
Location: Denver, CO
Points: 197842

Post Posted: Tue Nov 22, 2011 8:02 am Reply with quote    Back to top    

joycerecacho wrote:
I don´t know how many values the register has, so, the solution which uses the transform doesn´t work, right?

It does, you just have to pick a maximum number and build to that. Sure, the pivot will create a large number of "empty" rows but you can filter them out post-pivot.

_________________
-craig

You may ask yourself, "Well, how did I get here?"
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 38745
Location: Denver, CO
Points: 197842

Post Posted: Tue Nov 22, 2011 8:08 am Reply with quote    Back to top    

At a high level, the FAQ posting details writing a routine to loop thru the delimited string and building one long string where the 'key' fields are repeated per each pivoted column and then a record terminator (LF or CR/LF) concatenated into the string between each 'record'. What you get is one long record that when read back after writing it out get automagically turned into many records.

With 8.5 you could use the looping capabilites in the transformer for this rather than having to write a routine.

_________________
-craig

You may ask yourself, "Well, how did I get here?"
Rate this response:  
Not yet rated
datisaq
Participant



Joined: 14 May 2008
Posts: 154

Points: 1363

Post Posted: Tue Nov 22, 2011 8:53 am Reply with quote    Back to top    

You can write a shell script for the dynamic pivot.
I have one you can use this as reference and change accordingly


Code:
#!/usr/bin/ksh

###########################################################################
#
#
# Script Name:  DynamicPivot.sh
#
#
# Input:        DynamicPivot.sh {Input File Absolute Path}
#
#
# Output:       Output File Absolute Path
#      0 - for success
#               Error Description - for failure
#
#
# Description:
#
#               This script gets executed on the command line, with
#               one arguments (see Input).
#               This will perform the dynamic pivoting mechanism.
#               
#               This will pivot the data of file having two fields
#      one file which is key field(Not Nullable) and the other field(pivot field)
#      
#
###########################################################################
###########################################################################
#
# Setting the standard variables
#
###########################################################################

PROG=`basename ${0}`
EXIT_STATUS=0
NOW=`date`

###########################################################################
#
# Check command line arguments
#
###########################################################################

if [ ${#} -ne 2 ]
then
   echo "${NOW} ${PROG}: Invalid parameter list."
   echo "${NOW} ${PROG}: The script needs 2 parameters:"
   echo "${NOW} ${PROG}: Source File Absolute path and Target File Absolute path"
   exit 99;
fi

INPUT_FILE_PATH=${1}
OUTPUT_FILE_PATH=${2}

cat /dev/null > ${OUTPUT_FILE_PATH}

while read i
do
field1=`echo ${i} | awk -F '|' '{print $1;}'`
cnt_fld2=`echo ${i} | awk -F '|' '{print $2;}' | awk -F ',' '{print NF;}'`

j=1
if [ cnt_fld2 -eq 0 ];then
echo ${field1}"|" >> ${OUTPUT_FILE_PATH}
else
while [ j -le ${cnt_fld2} ]
do
echo ${field1}"|"`echo ${i} | awk -F '|' '{print $2;}' | awk -F ',' -v name="$j" '{print $name;}'` >> ${OUTPUT_FILE_PATH}
j=`expr ${j} + 1`
done
fi
done < ${INPUT_FILE_PATH}
exit 0;

_________________
IBM Certified - Information Server 8.1
Rate this response:  
Not yet rated
joycerecacho
Participant



Joined: 26 Aug 2008
Posts: 179

Points: 3404

Post Posted: Tue Nov 22, 2011 9:09 am Reply with quote    Back to top    

Guys, thank u for your replies!!!!!

I´ll try the shell script, I think it can help me a lot!!!!!!!!

Best Regards,

_________________
Joyce Recacho
Brazil / SP
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 51688
Location: Canberra, Australia
Points: 280710

Post Posted: Tue Nov 22, 2011 2:12 pm Reply with quote    Back to top    

You could also convert the commas to value marks (@VM) and write to a hashed file, then read from the hashed file normalized on the multi-valued column.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong
currently hiring: Sydney and Melbourne
Rate this response:  
Not yet rated
joycerecacho
Participant



Joined: 26 Aug 2008
Posts: 179

Points: 3404

Post Posted: Thu Dec 01, 2011 5:16 am Reply with quote    Back to top    

Hi Guys,

At the end of the story, I created a routine which counts the number of delimiters in a list and sums one.
Example:
Joyce, John, Mary

Delimiters = 2 (+1) = "3"

Then, I wrote a FOR loop starting from 1 and going until Delimiters quantity.

Inside de Loop, it generates a string and between the values I put 'Char(10)' to break down in lines.
So, in the examples above, the result was: JoyceChar(10)JohnChar(10)Mary(Char10)
If I read this file, would be:

Joyce
John
Mary

It worked very well, was simple and fast.

Thank you guys.

Best Regards,

_________________
Joyce Recacho
Brazil / SP
Rate this response:  
Not yet rated
karthi_gana



Group memberships:
Premium Members

Joined: 28 Apr 2009
Posts: 683

Points: 6228

Post Posted: Sun Feb 26, 2012 3:25 am Reply with quote    Back to top    

Ray,

Quote:
...the hashed file normalized on the multi-valued column.


i dont understand this context. can you pls explain it?

_________________
Karthik
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours