1. Categories >> Software >> Data Warehouse >> Informatica
  2. Suggest New Category

The structure of source file is as below:
Source structure(two fields)
Name, Card NUmber
A, 111111111(SSN)
A, 01010101(Creditcard number)
A, 34343434(Debit card number)
B, 55555555(Creditcard number)
C, 77777777(Debit card number)


Target Structure(4 fields)
Name,Credit card,SSN,Debit card
A,01010101,111111111, 34343434
B,55555555,,
C,,,77777777

Corresponding to one name there can be maximum 3 rows and
minimum zero rows.
Given that I do not know which record might have a
particular type of number.
How can I handle above requirement with informatica
transformations?

Question Posted / abhishek kumar

Answer Posted / abhishek kumar

in this qn ,if B and C will be SSN THEN BY THROUGH RANK
T/F , AGGEREGATOR WE CAN SOLVE THE PROBLEM.
RANK T/F
1)NAME- GROUP BY
2)CARD NUMBER -DO RANK ON NAME COLUMN
3)IN PROPERTIES TAB RANK OCCURENCE IS 3
4)TOP/BOTTOM-- BOTTOM
IN RANK T/F OUTPUT WILL BE AS BELLOW:

RANK INDEX NAME CARDNUMBER
1 A 111111111
2 A 01010101
3 A 34343434
1 B 55555555
1 C 77777777

IN AGGREGATOR
1)CREATE 3 OUTPUT PORT AS credit card,ssn,debit card
2)group by name
3)ssn (o/p)<-
first(decode(rankindex,1,name)
4)credit card(o/p)<-
first(decode(rankindex,2,name)
5)debit card(o/p)<-
first(decode(rankindex,3,name)

output will be
NAME,SSN,CREDIT CARD,DEBIT CARD
A,111111111,01010101,34343434
B,55555555,,
C,77777777,,

THANKS
ABHISHEK GUDDU

Is This Answer Correct ?    2 Yes 5 No



Post New Answer       View All Answers


Please Help Members By Posting Answers For Below Questions

How to generate or load values in to the target table based on a column value using informatica etl tool.

619

What is update strategy transform?

584

what is degenerated dimension?

3164

Can you generate sequence numbers without using the sequence generator transformation?

605

What are different types of transformations available in informatica?

593




What do you mean by worklet?

641

What is the commit type if you have a transaction control transformation in the mapping?

659

What are the new features of Power Center 5.0?

1242

What is the benefit of session partitioning?

606

Mention few power centre client applications with their basic purpose?

546

explain abt ur project architecture?

1769

Explain in detail about scd type 1 through mapping.

597

How you prepared reports for OLAP?

1163

What is difference between a gateway node and worker node?

637

without table how to come first record only in oracle?

1864