Help in creating single record from multiple records

[RKTEK]

I am new to informatica and I was given an assignment to design the mapping in order to achieve this. Your help is really appreciated. We are using informatica 7.11

We have a source table where the data looks like: (all blanks are nulls)

These are the conditions/constraints:

The Column 1 is the KEY for a target table.
The column 4 can be null.
There should not be two values in column 2 (X, E) for a unique column-1 (for 1). (This is a business condition). Same case with other columns.
No joins/lookups should be used.
Performance is more IMPORTANT as the table is very large in size.

Source Table:
Column 1 Column 2 Column 3 Column 4
1 X
1 X
1 X
2 Y
2 Y
2 Y
1 E
3 Z
3 Z
3

Target Table:
This is the target table should be achieved.

2 Y Y Y
3 Z Z

This is the error file should look like:
ERROR TABLE/FILE
1 X X X
1 E X X

 

[blom0344]

Create aggregate transformation with group by on column 1 and use max on columns 2,3,4. the outcome is:

a. all values for columns 2,3,4 are the same. This is the output you write to target.
b. Values for column 2 and 3 are the same and column 4 is null (or zero), write to target

(this last detail depends on the server setting ‘ treat aggregate nulls as zero’ )

c. value of column2 differs from colums 3 and 4; write this one to error table and additional row that contains value of column 3/4 and substitute in column 2.

You can extend this structure to your needs, but I think you may only need 1 agg transformation, a router (and perhaps additional filters)

This is not by any chance some sort of exam question?


Ties Blom

 

[RKTEK]

Thanks for the response. This question was asked in Yahoo interview.

But, I think my question has given a wrong impression.

Actually the column 2,3,4 are not the same. They dont have to contain same values. My example was wrong. I put 'X' by mistake.

Sorry for the confusion:

It was supposed to be: (all blanks are nulls)


Source Table:
Column 1 Column 2 Column 3 Column4

1 X
1 Y
1 Z
2 A
2 B
2 C
1 D
3 E
3 F


Target Table:
This is the target table should be achieved.

2 A B C
3 E F

This is the error file should look like:
ERROR TABLE/FILE
1 X Y Z
1 D Y Z (Or it can be '1 D null null')

If I use aggregator/group by, I am not going to catch these errors. instead it will insert one of them into target table.

Hope you can answer this. Thanks

 

[blom0344]

Well, I think this is not really within this sites policies, but here's my 2 cents:

If your example is not any more complex than this, then use agregator with 6 outgoing ports: a min and max for each of the 3 incoming ports 2,3,4. Group by port 1.

Now evaluate if - for each port - min / max values are identical, then write this to target.
If not then you have a bunch of scenario'sthat you need to resolve before writing to the error table..

Ties Blom