Page 1 of 1
combine 5 records into one records
Posted: Wed Mar 11, 2020 12:57 am
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
Posted: Wed Mar 11, 2020 8:13 am
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?
Posted: Wed Mar 11, 2020 10:38 am
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)
Posted: Wed Mar 11, 2020 1:04 pm
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?
Posted: Thu Mar 12, 2020 1:01 am
by rakesh.puli
that's my mistake.yes its for all records.
Posted: Sat Mar 14, 2020 2:10 pm
by chulett
Okay, seems like your stage variable approach could be made to work but instead of LastRowInGroup() I would try using LastRow() instead.
Posted: Thu Mar 26, 2020 10:35 am
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
Re: combine 5 records into one records
Posted: Sun May 03, 2020 5:56 am
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
Re: combine 5 records into one records
Posted: Mon May 04, 2020 9:11 am
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