INPUT file 'A' contains:
1
2
3
4
5
6
7
8
9
10
input file 'B' contains:
6
7
8
9
10
11
12
13
14
15
Output file 'X' contains:
1
2
3
4
5
Output file 'Y' contains:
6
7
8
9
10
Output file 'Z' contains:
11
12
13
14
15
How can we implement this in a single ds job?
- 11 Answers
- 14892 Views
- CTS, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Yes Union of Two Files is a Good idea
but No need of Transformer Stage we can do it in this way:
src1 ,src2 -->Funnel--->Sort--->Filter---->trg1,trg2,trg3
at sort just put allow duplicates=false
&
at filter just give conditions
1. col1>=1 and col1<6 trg1
2. col1>=6 and col1<11 trg2
3. col1>=11 trg3
| Is This Answer Correct ? | 9 Yes | 3 No |
Answer / noothan
This is another option for implementing the same.
Implement a full outer join with File A as Left and File B
as right. Let the output contain Col_File_A from file A and
Col_File_B from file B. Pass this output through a filter
stage with the filter condition as below.
Col_File_B is null= file x
Col_File_B is not null and Col_File_A is not null = file Y
Col_File_A is null= file Z.
| Is This Answer Correct ? | 4 Yes | 0 No |
Answer / subbuchamala
1)
We can solve this by using Change capture stage. First,we use source as 'A' and refrerence as 'B' both of them are connected to Change capture stage. From, change capture stage it connected to filter stage and then targets X,Y and Z.
(Change Code '0'--->exist in both, copy record
Change Code '1'--->Exists in SRC 'A', INSERT record
Change Code '2'--->Exists in SRC 'B', DELETE record
Change Code '3'--->Exists in 'A' & 'B' with changes, UPDATE record)
In the filter stage:
Change_code column=1 it goes to 'X' [1,2,3,4,5]
Change_code column=0 it goes to 'Y' [6,7,8,9,10]
Change_code column=2 it goes to 'Z' [11,12,13,14,15]
2)
Add an extra column 'colA' and 'colB' to the files 'A' and 'B' respectively. Let the value for colsA be 'a' for all the rows in file 'A' and the value for colB be 'b' in file 'B'(using the column generator stage).Now join both the files using join stage using 'ID' column. Perform full outer join. Map the ID col, colA and colB to output. Next pass it through a transformer.
Transformer constraint:
1) file X - colA=a and colB<>b ----->[1,2,3,4,5]
2) file Y - colA=a and colB=b------->[6,7,8,9,10]
3) file Z - colA<>a and colB=b------>[11,12,13,14,15]
| Is This Answer Correct ? | 5 Yes | 3 No |
Answer / shar
alternative ans lot similar to subu's.
to both files add a dummy column let say
File A
col1,dummy1
1,1
2,1
3,1
4,1
5,1
6,1
7,1
8,1
9,1
10,1
And for File B
col1,dummy2
6,2
7,2
8,2
9,2
10,2
11,2
12,2
13,2
14,2
15,2
Using col generator . Now join the two files using join
stage with full outer join. With key col1.
Then the output will be:
Left_col1, dum1, right_col1, dum2
1 1 0 0
2 1 0 0
3 1 0 0
4 1 0 0
5 1 0 0
6 1 6 2
7 1 7 2
8 1 8 2
9 1 9 2
10 1 10 2
0 0 11 2
0 0 12 2
0 0 13 2
0 0 14 2
0 0 15 2
Now assign the output to Transformer stage where at
constraints we mention:
DSLink6.dummy1 = 1 And DSLink6.dummy2 <> 2
DSLink6.dummy1 = 1 And DSLink6.dummy2 = 2
DSLink6.dummy1 <> 1 And DSLink6.dummy2 = 2
Then at three targets we map
Target 1 : left_col1
Target2 : left_col1
Target3: Right_col1
Hence the o/p.
| Is This Answer Correct ? | 2 Yes | 1 No |
Shar,
You explained the same as my 2nd method with examples.
Thanks
Subhash
| Is This Answer Correct ? | 1 Yes | 0 No |
Use Full outer join
Right ------------->X table
| |
| |
Left--> Join----->Filter---------->Y table
|
|
Z table
Left.colname=Right.colname
Right.colname='' (A-B)
Left.colname<>'' and Right.colname<>'' (A intersection B)
Left.colname<>'' (B-A)
Will get desired output
Please correct if am wrong....
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / shar
ok i shouldn't have hard coded with values.
Can u plz be more clear with the solution u gave with stages
and flows properly.
Thanks in advance.
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / sreenivas ramanathan
We can achieve this by using Change data capture stage as comparing the 2 inputs.
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / huma
Why're we complicating the solution. Simple use merge stage. Output Y will be result of merge (inner join) while rest all will be the rejected outputs from update link.
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / vaibhav
Hi Subbuchamala..
I marked this ans as no bcoz CDC stage works row by row i.e. on key column basis.. here for all the 10 records change_code will be 3 so it cannot be differentiated into 3 target files...
Different approach will be.. take union of 2 input tables and use transformer stage and filter on the basis of rownumber..
Let me know if I am wrong..
| Is This Answer Correct ? | 1 Yes | 2 No |
how to export or import the jobs in .ISX file
What is the difference between SQl Loader and OCI in datastage?
Enlist various types of routines in datastage.
Why we use parameters instead of hard code in datastage.
I have the following columns in the EMP table Empid,Empname,Sal,month(Sal),year(Sal) and DOB(let us say the dob is 15th-Jan-1981) Desing a job such that the output contains the following empname,year(sal),tot(sal) and current age i.e. whether 18yrs or so on
Give an idea of system variables.
If there is a file that contains 1000 records, I need the ouput to contain these 1000 records with the header as file name concatenated with the current timestamp and trailer as the count of records
Tell me the syntax of Configuration file?
IN SEQUENTIAL FILE,I HAVE DATA LIKE THIS EID,ENAME 11,AA 11,CC 22,DD 33,EE 22,AA 22,BB 22,CC 11,BB THEN i SELECT perform sort ON eid,uncheck both unique,stable AND I CHOOSE hash SORTING.WHAT IS THE OUTPUT I CAN GET.what happend if i select UNIQUE,STABLE. PLEASE EXPLAIN HOW THE RECORDS DISPLAY AT TARGET.
i/p o/p1 o/p2 1 1 4 1 1 5 1 1 6 2 2 2 2 2 2 3 3 4 5 6 how to populates i/p rows into o/p1&o/p2 using datastage stages?and also the same scenario using sql?
What is the difference between server job and parallel jobs?
can we use sequential file as a lookup
Teradata 
