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,
Answers were Sorted based on User's Feedback
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 |
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 |
in source is like seq file in date column have dd-mm-yy dddd-mmmm-yyyy mm-dd-yy yy-dd-mm yy-mm-dd i want to display only yy-dd-mm date formats only in tgt?
What r the existing server jobs in parallalism?
if ename='subbu' while running job the job should be abort how come?
I have scenario like this seq file --> transformer --> sort stage --> dataset In this job which prtitioning technique you are using and why???
is it possible to access the same job by two users at a time in DataStage?
What is the use of surrogate key stage?
iam new to datastage...now i want to know what are fact tables, dimension tables in bank domain...if any body knows plz tell me asap..
How many Nodes configuration file you used in your last project?
HOW CAN WE ABORT THE JOB IF THE RECORDS OF SOURCE FILE CONTAINS VALUE AS 'VIJAY'?
Different ways a project can be moved to production ?...example ….export-import and Version control.
in oracle target stage when we use load option and when we use upsert option?
In a batch if a job fails in between and you want to restart the batch from that particular job and not from the scratch then what will you do?