# Datastage Job Scenario question

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 do in this in a single ds job in px ?....could you please give me the logic to implement ???

• Jul 27th, 2011

Hello guy's, I would like to solve this by using the Change capture stage. First, i am going to 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. In the filter stage: keychange column=2 it goes to X [1,2,3,4,5] Keychange column=0 it goes to Y [6,7,8,9,10] Keychange column=1 it goes to Z [11,12,13,14,15] Revert me PLz

#### mcrao1 Profile Answers by mcrao1

• Jul 11th, 2011

Hi Srinivas

In your scenario we need use two processing stages,1. Funnel stagge and transformer stage.In your scenario total 2 input files are giving with different values, the 2 files we need to club by using funnel stage continues option. next we need to take transformer stage.In this transformer stage we need to apply constraint based on that we can split in to 3 files.   constraint u need to apply like:

Regards
Chalapathirao.M

HCL Technologies.(Malaysia)
+60182296096.

#### srinivasarao

• Jul 13th, 2011

Use two source to conn to funnelstage and generate seqno to swichstage give three condition for switch stage
nextval<=5
nextval>5 and netval<=10
nextval>10
connet to the third target

#### vinod chowdary

• Jul 27th, 2011

Hello guy's,

I would like to solve this by using the Change capture stage. First, i am going to 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.

In the filter stage: keychange column=2 it goes to X [1,2,3,4,5]
Keychange column=0 it goes to Y [6,7,8,9,10]
Keychange column=1 it goes to Z [11,12,13,14,15]

Revert me PLz

#### naresh

• Jul 29th, 2011

Hi,
First you will merge 2 seq files in a singe file(seq file stage),sort the data and remove the duplicates ( in SORT STAGE),take filter (< 6) pass main link(T1),and take reject data (< 11) pass main(T2) link and reject data as T3

#### Dinesh Babu Durai

• Jul 29th, 2011

this logic is wrong..

in the 2nd link you will be getting duplicates. but the above y-output says onlly 6,7,8,9,10

#### Shiva

• Aug 1st, 2011

Simple Ans:

First of all take a join stage like a full outer join, then it will join both tables, After that o/p is {1,2,3....15}

then take the Filter stage for what columns you are need then that columns are you can filter.

#### SinhaS Profile Answers by SinhaS

• Aug 5th, 2011

Hi,

I would use seq file stage and merge the two files in by choosing read method of File Pattern. Then choose a sort stage to sort by ascending order and remove duplicates. Then use filter to output three links x, y and z to create the three respective files by giving filter conditions col_name < 6 , col_name > 5 and col_name < 11 , col_name > 10. Would take care to use 'Sort Merge' Collection on the three output files (X,Y and Z) so that the ordering is conserved.

#### dileepkumar

• Aug 9th, 2011

By using transformer and remove duplicate stage we can do this scenario.

#### KNVD Pavan Kumar

• Aug 11th, 2011

You take two seq files and club the files in to funnel stage as continuous funnel and take sort stage and filter the duplicates and the n transformer stage write the constraints over there and give 3 outputs

That's all its very simple method !!!

#### Akila

• Apr 1st, 2013

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. 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
2) file Y - colA=a and colB=b
3) file X - colA<>a and colB=b

#### muralidhar

• May 30th, 2013

first funnel the two input files and output link connect to transformer and sort the input date. Apply constraints for 3 links.In 1st link @inrownum<=5 then we will data in x file as 12345.In 2nd link @inrownum>5 and @inrownum<=10 then will get in y file as 678910.In 3rd link otherwise or @inrownum>10 then will get data in z file as 1112131415.

#### sivaksa Profile Answers by sivaksa

• Aug 13th, 2013

We can use funnel...sort...trans...target 3outputsp

#### Viral Patel

• Aug 27th, 2013

The rest of the solutions are correct. But if u want to make the PX job completely dynamic. the following solution works.

F1-->Copy1 nd F2-->Copy2 .

From Copy1 nd Copy2 to a lookUP1(perform inner join). Output gives Target 2.

O/p of lookUP1 goes to lookUP2 with CPY1 as main link(perform inner join). Reject will give Target 1.

O/p of lookUP1 again goes to lookUP3 with CPY2 as main link (perform inner join). Reject will give Target 2.

#### Ankit Gosain

• Sep 17th, 2013

Hi,

In your scenario there are two i/p files i.e. A,B & according to my observation you want records which are there in A but not in B in the file X, Records which are there in the file A as well as in B in the file Y and Records which are there in the file B but not in A in the file Z.

You can do this in a single job by using Change Capture Stage by doing the following settings:

Change Capture I/Ps: A as AFTER & B as BEFORE.

then give the o/p of Change Capture to a Filter stage, and take three where clause for three seperate links X,Y & Z.

Filter Stage: where clause for X => ChangeCode=1 //Newly inserted records, which are there in A but not in B
where clause for Y => ChangeCode=0 //Copy Records, which are there in the file A as well as in B
where clause for Z => ChangeCode=2 //Deleted Records, which are there in the file B but not in A

So that youll have the generic solution for any number of records.

Cheers,
Ankit :)

#### rajesh chunduri

• Oct 21st, 2013

Hi,

Let consider this scenario for millions of records but not just for 10.
Here We have to use change capture stage (CCS). CCS needs to files as i/p, one as main file other called as reference file.
When we give I/P as this two files CCS generates unique key value for all records where we can say them as below.

Copy (existing record both file 1&2)-1
New ( file 1)-2
OLD ( file 2)-3

Now pass this records to files and apply condition on filter to flow data according to our requirement.
Now data (1-5),(6-10),(11-15) will be loaded to different files.

Thanks,
Rajesh Chunduri
TechM

• Jan 7th, 2014

As per your scenario, we can use two sequential datset with transformer stage. In join stage properties join type use as full outer join and write the conditions in transformer as dslink11.eid <=5

#### premox5 Profile Answers by premox5 Questions by premox5

• Jan 31st, 2014

if the input file metadata are same means you can use 1 seq file to read both the input files and then use filter stage and load it to the target files.

#### ghost

• May 17th, 2014

create one px job.
src file= seq1 (1,2,3,4,5,6,7,8,9,10)
1st lkp = seq2 (6,7,8,9,10,11,12,13,14,15)
o/p - matching recs - o/p 1 (6,7,8,9,10)
not-matching records - o/p 2 (1,2,3,4,5)
2nd lkp:
src file - o/p 1 (6,7,8,9,10)
lkp file - seq 2 (6,7,8,9,10,11,12,13,14,15)
not matching recs - o/p 3 (11,12,13,14,15)

#### Jithin

• Sep 11th, 2014

Do a full outer join between two files and from transformer draw three output links

1st link-->wherever left side is null
2nd link->wherever right side is null

#### sambit kumar rout

• Jul 1st, 2015

Use funnel stage to combine both the A and B source data. use RDC to remove delicacies in the result. Use transformer stage. Set @INROWNUM<=5 to destination X in one condition. In second condition give @INROWNUM=6 and @INROWNUM<=10 to destination Y and in last condition give @INROWNUM=11 and @INROWNUM<=15 to destination Z

#### Pavan Kumar

• Jul 30th, 2015

Using change capture stage:
File1(Master),File2----> change capture---> Filter ---->
T1, T2, T3
In Filter,
Change_code=1 then send then to T1-->(Insert records)
Change_code=0 then send then to T2-->(Copy records)
Change_code=2 then send then to T3-->(Delete records)

#### Priya Ranjan kumar

• Aug 28th, 2017

You can solve this question in easily way like.
First use the funnel to take the input data from different sources and used check box for unique data(No duplicate data) then
you can use transformer where you can apply logic like. @INROW <=5 pass all the column into output and default method must be in sequential order.
I think it might be helpful.