Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


one file contains
col1
100
200
300
400
500
100
300
600
300
from this i want to retrive the only duplicate like this
tr1
100
100
300
300
300 how it's possible in datastage?can any one plz explain
clearley..........?

Answers were Sorted based on User's Feedback



one file contains col1 100 200 300 400 500 100 300 600 300 from this i want to retrive th..

Answer / vinod upputuri

In order to collect the duplicate values:

first cal the count output col in aggregator stage
group by col.
aggregator type: count rows.
count output col..

next, use the filter stage to separate the multiple occurrence.

finally, use the join stage or lookup stage to map the two
tables join type INNER ..

then u can get the desired output..

Is This Answer Correct ?    14 Yes 1 No

one file contains col1 100 200 300 400 500 100 300 600 300 from this i want to retrive th..

Answer / chandu

use aggregator and calculate count of source column after
that use filter or transaformer stage use condition as count
>1 it gives only duplicate records


Thanks
Chandu-9538627859

Is This Answer Correct ?    6 Yes 0 No

one file contains col1 100 200 300 400 500 100 300 600 300 from this i want to retrive th..

Answer / prasad

>Agg--->Filter1------->|
| |
| |
file-->cp-------------------->Join---->Filter2---->target1
|
|
Target2
Agg: use aggregator and select Agg_type=count rows and then give the Count O/P column=Count (User defined)

Count
------------
100--2
200--1
300--3
400--1
500--1
600--1
it will generate in Agg stage then

Filter1: give condition like Count=1( u will get unique records from Filter1)

Join Stage: take Left Outer Join

Filter2:
where=column_name=''(null){u will get duplicates records)

Target1 o/p:
100
100
300
300
300

where= column_name<>''(u will get unique records)

Target2 o/p:

200
400
500
600

Please correct, if am wrong :)

Is This Answer Correct ?    2 Yes 0 No

one file contains col1 100 200 300 400 500 100 300 600 300 from this i want to retrive th..

Answer / sudheer

- aggregator -
seq. file - copy join - filter - seq.op


in arrg - cnt rows
in join - left outer join - left as seq.file data
in filter - where cond. - cnt>1

Is This Answer Correct ?    1 Yes 0 No

one file contains col1 100 200 300 400 500 100 300 600 300 from this i want to retrive th..

Answer / reddyvaraprasad

Job Design:

|----->Agg--->Filter1-->|
| |
| |
file-->cp-------------------->Join---->Filter2---->target

Agg: use aggregator and select Agg_type=count rows and then give the Count O/P column=Count (User defined).

Filter1: give the condition Count<>1

Join: select left outer join

Filter2: give the condition Count<>0

u will get the right output....what ever the duplicate records.

and if u want unique records, give the condition Count=0

Is This Answer Correct ?    0 Yes 0 No

one file contains col1 100 200 300 400 500 100 300 600 300 from this i want to retrive th..

Answer / reddymkl.dwh

Job Design:

Agg--->Filter1---------->|
| | Unique
file-->cp-------------------->Join---->Filter2---->target1
|
|-->Duplicate
Target2

Agg: use aggregator and select Agg_type=count rows and then give the Count O/P column=Cnt (User defined).

Filter1: give the condition Where=Cnt=1

U will get unique values like 200,400,500,600

Use Join (Or) Lookup stage: select left outer join

Filter2:

Where=Column_name='' (Duplicate values like 100,100,300,300,300)
Where=Column_name<>'' (Unique Values like 200,400,500,600)


u will get the right output....what ever the duplicate records.

Plz correct me if am wrong.....

Is This Answer Correct ?    0 Yes 0 No

one file contains col1 100 200 300 400 500 100 300 600 300 from this i want to retrive th..

Answer / pooja

Follow the following steps -

1. Seq file stage - Read the input data in seq file - input1.txt
2. Aggregate stage - count the number of rows (say CountRow) for each ID(group=ID)
3. Filter stage - Filter the data where CountRow<>1
4. Perform join on the output of the step 3 and input1.txt.
You will get the result :)

Is This Answer Correct ?    0 Yes 0 No

one file contains col1 100 200 300 400 500 100 300 600 300 from this i want to retrive th..

Answer / me

seq----> copy

from copy stage one link to aggregator apply count rows option ---> filter (on count rows output 1 ) send as reference to look up below
from copy stage second link to lookup

apply filter

Is This Answer Correct ?    0 Yes 0 No

Post New Answer



More Data Stage Interview Questions

Is it possible to implement parallelism in Mainframe Jobs ? If Yes how ? If no why ?

0 Answers   IBM,


source file having the data like aabbccc, i want target file result like a1a2b1b2c1c2c3.

2 Answers  


SOURCE CITY NAME1 NAME2 NAME3 NAME4 BGLR HCL CSC IBM TCS TARGET CITY COMPANY BGLR HCL BGLR CSC BGLR IBM BGLR TCS

1 Answers  


how can you generate sequence number using only the sequential file stage?

3 Answers   TIAA CREF,


i have a scenario with i/p as ID,salary with values 1,1000 2,2000 and 3,4000 i need an extra column in the o/p named amount with values 2000,4000 and NULL. how can i get it?

2 Answers   L&T,






Hi,can any one please mention list of dimension and fact tables for Sales and Distribution System for a Pharmacutical company.

0 Answers   Syntel,


DB2 connector> transformer > sequential file Data will be exported into a csv format in a sequential file. This file will be send in a email using a sequence job. Problem here is, how to avoid sending a blank csv file? When I ran the job there are chances that it might return zero records but in the sequence job csv file is going blank. how can I avoid this? thanks

0 Answers  


Hi This is Vijay, How can your remove the duplicates in sequential File?

15 Answers   HCL, Scope International,


LIST THE BUILT-IN STAGES AVALIABLE IN SERVER JOBS?

2 Answers   Mannar Company, ME,


what is the difference between lookup stage reject link and merge stage reject link in datastage Parallel jobs? interm of output in Merge Reject link and Look Up Reject link ?

4 Answers   HCL,


How did you reconcile source with target?

3 Answers   HP,


what is normalization and denormalization

2 Answers   CTS,






Categories