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 ???

Questions by Boopathy Srinivasan

Editorial / Best Answer

Answered by: vinod chowdary

  • Jul 28th, 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

Showing Answers 1 - 39 of 39 Answers

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:

DSLink15.rowid < 5
DSLink15.rowid  >5 and  DSLink15.rowid  < 10
DSLink15.rowid  >10 and  DSLink15.rowid < 15 


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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Dinesh Babu Durai

  • Jul 29th, 2011
 

this logic is wrong..
DSLink15.rowid < 5
DSLink15.rowid >5 and DSLink15.rowid < 10
DSLink15.rowid >10 and DSLink15.rowid < 15

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

  Was this answer useful?  Yes

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

  • 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.


  Was this answer useful?  Yes

dileepkumar

  • Aug 9th, 2011
 

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

  Was this answer useful?  Yes

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 !!!

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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.


  Was this answer useful?  Yes

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 :)

  Was this answer useful?  Yes

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




  Was this answer useful?  Yes

Penchala Prasad

  • 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
dslink11.eid >5 and dslink11.eid<=10 and third condition is dslink.eid >10

  Was this answer useful?  Yes

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)

  Was this answer useful?  Yes

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
3rd link->wherever match is there

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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)

  Was this answer useful?  Yes

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.
please share your feedback.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions