DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
rakesh.puli



Group memberships:
Premium Members

Joined: 22 Mar 2017
Posts: 28

Points: 315

Post Posted: Wed Mar 11, 2020 12:57 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Can any one please explain how to achieve this scenario in datastage using transformer?

Code:


Input

ColA
1
2
3
4
5

Output
ColA
(1,2,3,4,5)

there is no common key in the input 5 records.Since there is no common key,I think like we cannot use lastrowingroup() function in transformer. I tried like this but i didnt get the ouput

i have designed the job like below


seqfile---sortstage--transformer-output

in transformer i created stage variable like below

svtemp: if (keychangecolumn=1) then svtemp:",":COLA else COLA


and then assigned this "svtemp" to output colB. But I am not getting the required ouput.Please help me on this issue.

thanks
Rakesh
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 43085
Location: Denver, CO
Points: 222463

Post Posted: Wed Mar 11, 2020 8:13 am Reply with quote    Back to top    

Well... at its core, that is a vertical pivot of rows to columns. However, your example doesn't quite cover the scope of the issue. You only show five records total but do you need to pivot every five records into one record? Meaning, 20 input records = 4 output records?

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
rakesh.puli



Group memberships:
Premium Members

Joined: 22 Mar 2017
Posts: 28

Points: 315

Post Posted: Wed Mar 11, 2020 10:38 am Reply with quote    Back to top    

No. It should not be 4 output records. Suppose, If there are 20 records then output should be one record with one filed as (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
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: 43085
Location: Denver, CO
Points: 222463

Post Posted: Wed Mar 11, 2020 1:04 pm Reply with quote    Back to top    

Ah, okay... was going by the subject of your post since you said "5" rather than "all". And let me guess, you have no idea what the maximum number of records you might have to pivot would be?

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
rakesh.puli



Group memberships:
Premium Members

Joined: 22 Mar 2017
Posts: 28

Points: 315

Post Posted: Thu Mar 12, 2020 1:01 am Reply with quote    Back to top    

that's my mistake.yes its for all records.
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: 43085
Location: Denver, CO
Points: 222463

Post Posted: Sat Mar 14, 2020 2:10 pm Reply with quote    Back to top    

Okay, seems like your stage variable approach could be made to work but instead of LastRowInGroup() I would try using LastRow() instead.

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
rakesh.puli



Group memberships:
Premium Members

Joined: 22 Mar 2017
Posts: 28

Points: 315

Post Posted: Thu Mar 26, 2020 10:35 am Reply with quote    Back to top    

Hi Chulett,

Thank you for your reply.

I tried using Lastrow() function, But my approach is not working. where exactly we have to use this LastRow() function in the derivation.
I tried solving this with below two methods,but I didn't get the desired output.Can you please let me know how to achieve this with lastrow() function.

I modified the earlier code. I created 3 stage variables as below and assigned the one stage variable to output column

Code:



Method1

Input column                            Derivation                                                                    stage variables

item                              if LastRow(item) then 'Y' else 'N'                                                  Islastrow

keychange                        if keyChange=1 then item:",": svtemp  else ""                                        totalitemlist

                                 if keyChange=1 then "" else item:",": svtemp                                         svtemp

                               
                                      output
                             

                                    Constraint:

Derivation                                   columnname

totalitemlist                                   itemout





Method 2:



                             stage variables

Input column                    Derivation                                                                      Stage variable

item                        if lastrow(item) then 'Y' else 'N'                                                   Islastrow
                           

                            if Islastrow='y' then item:",":svtemp  else ""                                       totalitemlist
                                                                                                     
                           
                       
                           if islastrow='Y' then "" else  item:",": svtemp                                        svtemp
                                                                                 

                                       
                                   output

                              Constraint:
 Derivation                                   columnname

totalitemlist                                   itemout




Thanks
Rakesh
Rate this response:  
Not yet rated
poorna.ds
Participant



Joined: 22 Jun 2008
Posts: 18
Location: Mumbai
Points: 173

Post Posted: Sun May 03, 2020 5:56 am Reply with quote    Back to top    

Hi Rakesh,

I found a solution to your query:
SRC--> Transformer --> TGT

In Transformer :
Stage Variables ->
svar1 : If IsNotNull(DSLink2.COL) then svar1:',':DSLink2.COL else svar1
svar2 : LastRow()

Constraint : svar2
output column = svar1

Output : 1,2,3,4,5,6,7,8,9,10
Rate this response:  
Not yet rated
rakesh.puli



Group memberships:
Premium Members

Joined: 22 Mar 2017
Posts: 28

Points: 315

Post Posted: Mon May 04, 2020 9:11 am Reply with quote    Back to top    

Thank you for your reply Poorna.I will try this.


poorna.ds wrote:
Hi Rakesh,

I found a solution to your query:
SRC--> Transformer --> TGT

In Transformer :
Stage Variables ->
svar1 : If IsNotNull(DSLink2.COL) then svar1:',':DSLink2.COL else svar1
svar2 : LastRow()

Constraint : svar2
output column = svar1

Output : 1,2,3,4,5,6,7,8,9,10
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