combine 5 records into one records

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
rakesh.puli
Premium Member
Premium Member
Posts: 29
Joined: Wed Mar 22, 2017 10:45 pm

combine 5 records into one records

Post by rakesh.puli »

Can any one please explain how to achieve this scenario in datastage using transformer?

Code: Select all


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
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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
rakesh.puli
Premium Member
Premium Member
Posts: 29
Joined: Wed Mar 22, 2017 10:45 pm

Post by rakesh.puli »

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)
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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
rakesh.puli
Premium Member
Premium Member
Posts: 29
Joined: Wed Mar 22, 2017 10:45 pm

Post by rakesh.puli »

that's my mistake.yes its for all records.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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
rakesh.puli
Premium Member
Premium Member
Posts: 29
Joined: Wed Mar 22, 2017 10:45 pm

Post by rakesh.puli »

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: Select all



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
poorna.ds
Participant
Posts: 18
Joined: Sun Jun 22, 2008 3:07 am
Location: Mumbai

Re: combine 5 records into one records

Post by poorna.ds »

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
rakesh.puli
Premium Member
Premium Member
Posts: 29
Joined: Wed Mar 22, 2017 10:45 pm

Re: combine 5 records into one records

Post by rakesh.puli »

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
Post Reply