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.

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

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

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.

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

Answer / bala

in router count(*)=1 for good record

count(*)>1 for duplicate record

Answer / apoorva

question:

shouldn't the condition for unique_record be >= ??

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.

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.

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)

how we can do session partition in informatica any one explain me clearly? thanks advance.

1 Answers   iGate,

Where do we use mapping variables in informatica?

0 Answers  

HAI FRIENDS THIS KISHORE FROM KUMBAKONAM. I HAVE COMPLETED MY MCA IN SASTRA UNIVERSITY WAITING FOR MY 6 TH SEM RESULT.DURING MY PROJECT I HAVE DONE INFORMATICA COURSE AS MY AREA INTEREST COURSE IN CORE MIND TECHNOLOGIES CHENNAI.I HAVE ENQUIRY ABT ALL IT PEOPLE FOR DATAWAREHOUSING THERE IS NO OPENING FOR FRESHERS. WITH SOME EXPERIENCE ANY OTHER DOMAIN TNEN ONLY U CAN GET THAT DOMAIN(DATAWAREHOUSING) JOB.BUT I AM MORE INTEREST ON THAT TO WORK ON THAT DOMAIN(INFORMATICA).WHAT TO I DO. I AM IN HELPLESS FRIENDS/EXPORTS.WAITING FOR REPLY

0 Answers  

what are the output files that the informatica server creats during running a session?

2 Answers   CTS,

What is critical mapping?

2 Answers  

What are the limitations of joiner transformation?

0 Answers   Informatica,

Slowly changing dimensions, types and where will you use them

0 Answers  

What is informatica? What exactly are the practical uses in real world

2 Answers  

If session fails after loading 10000 records in the target,how can we load 10001 th record when we run the session in the nexttime?

8 Answers   HeadStrong, TCS,

explain about informatica server architecture?

1 Answers   IBM,

Explain the etl program with few examples.

0 Answers  

How can yoU improve session performance in aggregator transformation?

8 Answers   TCS,