Search code examples
informaticainformatica-powercenter

Infomatica Row Count

I am new to Informatica and I am confused.

I have data from a flat file and need to do some transformation for it. I just need a general idea on how to actually do it.

Say I have data that looks like this:

COL1, COl2, COl3, COL4

A B C D

A B B B

G G G G

B D D X

F F F F

B B A D

1) I need to transfer only rows that have the first column as A or B

2) I need the count of the rows that are A, and I need a separate count that is B

3) I need a comparison of the count of A and the count of B. If the count do not match then I need an email sent.

Can someone give me a link to something helpful or tell me exactly that types of transformation / logic I should be using? Thanks

Solution

  • There are multiple ways. Here's a simple one, step-by-step.

    1. Use a filter on Source Qualifier to get just the data you need.
    2. Separate into two pipelines using Router Transformation with two groups defined as COL1='A' and COL1='B'
    3. Use Aggregate Transformation to get the counts (for each pipeline).
    4. Use Expression Transformaiton to add a dummy port e.g. joinPort = 1 (for each pipeline).
    5. Join the piplelines on dummy port using Joiner Transformation
    6. Use Expression Transformation to compare the results.

    Sending email a separate story.

    1. Use a Workflow variable e.g. wfSendEmail initialized to 0 and a mapping variable e.g. mSendEmail
    2. On session Components tab do the Pre-session variable assignment and assign wfSendEmail to mSendEmail.
    3. In the exression transformation mentioned in p.6 above use the SETVARIABLE function if the counts do not match to set the mSendEmail to 1.
    4. On session Components tab do the Post-session variable assignment and assign mSendEmail value to wfSendEmail.
    5. Add and Email task with a condition wfSendEmail=1 on the link from the session.