Question 4) source target c1 c1 c2 c3 c2 c4 c4 c5 c3 c

Question
4)
source target

c1 c1 c2 c3
c2 c4 c4 c5
c3 c6 c7
c4
c5
c6
c7

Singal Source and Singal Target only subash,

Question Posted / rathdsetl@gmail.com

3 Answers
7498 Views
TCS, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Question 4) source target c1 c1 c2 c3 c2 c4 c4 c5 c3 c6 c7 c4 c5 c6 c7..

1. SRC---->TFM---->PIVOT Stage---->TGT
If source is seq file add the ROW NUM column also.
If source is Oracle select ROWNUM column also.
then our input look like this:
COL1 COL2
C1 1
C2 2
C3 3
c4 4
c4 5
c5 6
c6 7
C7 8

in TFM,
put below condition for Col2 derivation.
If COL2<4 AND COL2>0 then 1 elseIf COL@>3 AND COL2<7 then 2 else 3
then out from tranformer looks like this
COL1 COL2
C1 1
C2 1
C3 1
c4 2
c4 2
c5 2
c6 3
C7 3
then do the Vertical PIVOT on
GROUP BY COL2,
PIVOT COL1
then u will get below output:
Col2 COL1_1 COL1_2 COL1_3

1 c1 c2 c3
2 c4 c4 c5
3 c6 c7
we can drop COL2 in TGT

Is This Answer Correct ?

6 Yes

1 No

Question 4) source target c1 c1 c2 c3 c2 c4 c4 c5 c3 c6 c7 c4 c5 c6 c7..

Answer / shar

HERE IS Mine answer an alternative without pivot stage:

SRC-->TX1-->TX2-->RemDup-->TRG

from SRC send data to TX1 with RowNumCol
then Data :
col1 Row_Num
c1 0
c2 1
c3 2
c4 3
c4 4
c5 5
c6 6
c7 7

Now in Tx1 take stage variable sv1 and using Row_Num field
give condition as : If Row_Num >=0 And Row_Num <3 Then 1
Else If Row_Num >= 3 And Row_Num <6 Then 2 Else 3
o/p :
col1 col2
c1 1
c2 1
c3 1
c4 2
c4 2
c5 2
c6 3
c7 3

(If you don't know Vertical Pivoting then alternatively we
use another Transformer and Remove Duplicate stages to get
our result)

now at Tx2 we take Three Stage Variables sv1 sv2 sv3 all are
varchar
now we give proper conditions to concatinate input using col2.
Stage variables: Assignment
col2---sv1
If sv1 <> sv3 Then col2 Else sv2 : ',' : col2 --- sv2
col2---sv3

Now at this level u should get o/p as:

newcol1 newcol2
1 c1
1 c1,c2
1 c1,c2,c3
2 c4
2 c4,c4
2 c4,c4,c5
3 c6
3 c6,c7

Finally use Rem Duplicates by selecting
Duplicates to Retain : Last
o/p
1 c1,c2,c3
2 c4,c4,c5
3 c6,c7

(Just in case if you dont want 1st column just drop it using
modify stage or copy stage.)
Yeah i know its a lengthy way and it sucks,
out of curiosity did it mannnnn.
Better use Pivot stage -a wonderful stage

Is This Answer Correct ?

0 Yes

0 No

Question 4) source target c1 c1 c2 c3 c2 c4 c4 c5 c3 c6 c7 c4 c5 c6 c7..

Answer / shar

small correction in answer at
sv1<>sv3 then col1 else col1:' ':sv2 --> sv2
and every thing is correct.

Is This Answer Correct ?

0 Yes

0 No

Post New Answer