How we can get unique records into one target table and
duplicate records into another target table??
Answers were Sorted based on User's Feedback
Answer / nag
Hi,
I think this scenario will help u.
Source - SQ Trans(2 Instances)-target(2 different targets)
In one of the Instances of SQ Trans write this Query:
select distinct <field name>......from <table name>;
Note:- It fetches only unique records.
In another Source instance write the following query:
select * from <table name> where rowid not in (select max
(rowid) from <table name> group by <field name>);
ex:- select * from emp where rowid not in (select max
(rowid) from emp group by ename);
Note:- It fetches only duplicate records.
then pass the fields to the targets(u should take different
targets).
I hope this will work.
Thanks and Regards,
Nag
Is This Answer Correct ? | 10 Yes | 3 No |
Answer / usha
1st method by using expression transformation by adding
variable ports.
2nd method by using dynamicLKP we can load UNIQ records to
one taget table and DUPLICATE records to other target table
newLKProw=1(Insert)
newlkprow=0(if there is no difference in associate ports)
UNIQ records
Is This Answer Correct ? | 10 Yes | 3 No |
Answer / hunny
Try This :
Source -> SQ -> Sorter(Sort by Ascending on Key) -> Aggregator(Group by key Column,var=count(key colmn) - > Router(var = 1) -> Unique Target and (var > 1 ) -> Duplicates.
Make sure the Datatype for Var is not String in this scenario else will encounter operands of ambigious types mismatch error.
Is This Answer Correct ? | 7 Yes | 0 No |
Answer / chetan
I/p
A
B
B
C
D
D
need to load A.C in one tgt and B,B,D,D in another target table.
SQ o/p should be given to aggregator. group by first column.create another port(v_count) with count(*).
SQ o/p should be passed to joiner to join aggr and SQ(source).
o/p looks like after joinning
A 1
B 2
B 2
C 1
D 2
D 2
use router to route v_count=1 and v_count=2 to different targets.
Is This Answer Correct ? | 6 Yes | 0 No |
Answer / naveen
Answer #3 is correct by Nag.
Addition to above second query for duplicate records, will
return unique records for each duplicate records.
if you need all duplicates records thn follow the below
query.
select * from emp
where empno in (select empno from emp group by empno having
count(empno)>1)
Regards,
Naveen
Is This Answer Correct ? | 6 Yes | 2 No |
Answer / bala
in router count(*)=1 for good record
count(*)>1 for duplicate record
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / apoorva
question:
shouldn't the condition for unique_record be >= ??
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / haaris
If the source is RDMS, then you can write analytic function in the select statement to assign count to each row. And use this count column to route the records to respective target.
If the source is a flat (this is generally asked in interviews), then you can use aggregator to assign count to each row after grouping based on the key and use joiner to join back to source records that way all the rows coming from the SQ will have respective counts. Use router to route them to respective targets.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / amedela chandra sekhar
sq-->Aggregator-->Router-->target
select group by for port name empno
in aggregater t/r take 1 output port and give condition like
select group by for port name empno
varcount---->count(*)
connect this one router and in router take 2 groups and
give condition like
uniqe_record :varcount=1-----for unique
duplicate_record :varcount>1 ----for duplicate
and connect that groups to target.
u will get result.
Is This Answer Correct ? | 7 Yes | 10 No |
Answer / chandra
FIRST WE TAKE SORTER Tr ,BY USING THIS WE CAN RETRIEVE
UNIQUE RECORDS...NEXT
WE TAKE EXP Tr IN THAT INSERT NEW OUTPUT PORT ..,IN THE
OUTPUT PORT EXPRESSION WRITE COUNT FUNCTION AFTER THAT TAKE
FILTER Tr IN THAT PUT THE CONDITION AS
IIF(COUNT>1,TRUE,FALSE)THEN CONNECT TO TARGET......
SQ-->SORTr-->TARTr1(UNIQUE)
-->EXPTr-->FIL Tr-->TAR Tr2(DUPLICATES)
Is This Answer Correct ? | 17 Yes | 30 No |
100 records in source and i have 3 targets, my requirement is first record insert into 1st target,2 record into 2nd target,3 record into 3rd target and again 4th record into 1st target like dat..how to implement dis?
What are the disadvantages of Sequence Generator T/R?
What type of sorting algorithm does the Sorter Transformation use, to do its sorting Operation? 1. Bubble sort 2. Insertion sort 3. Shell sort 4. Merge sort 5. Heapsort 6. Quicksort 7. Bucket sort
Is scd logic we have lookup right to compare the values with source and target,instead of lookup can u have any transformation or any way to do this?
What are the mapping paramaters and mapping variables?
Howmany ways yoU can update a relational source defintion and what are they?
How can i explain my project architecture in interview..? in Igate asked me tell me your project flow from source to target..?
1 Answers Ameri Group, CGI, CTS, HCL, iGate, TCS, Wipro,
explain about lookup t/r?
What are junk dimensions?
In pmcmd command we need to type -p:password. Will it be visible to others who is having access to this file ?
how to join the 2 different table with different columns in informatica?
Without using any transformations how u can load the data into target?