CODEWITHSUNDEEP
etllookupinformaticainformatica-powercenter
Swapandeep Singh
Swapandeep Singh

Reputation: 91

How to extract all rows, for which row a particular criteria is met? Details in description

I am trying to load a set of policy numbers in my Target based on below criteria using Informatica PowerCenter.

I want to select all those rows of policy numbers, for which policy the Rider = 0

This is my source: -

Policy  Rider   Plan
1234    0   1000
1234    1   1010
1234    2   3000
9090    0   2000
9090    2   2545
4321    3   2000
4321    1   2000

Target should look like this: -

Policy  Rider   Plan
1234    0   1000
1234    1   1010
1234    2   3000
9090    0   2000
9090    2   2545

The policy number 4321 would not be loaded.

If I use filter as Rider = 0, then I miss out on below rows: -

1234    1   1010
1234    2   3000
9090    0   2000
9090    2   2545

What would be ideal way to load this kind of data using PowerCenter Designer?

Upvotes: 0

Views: 114

Answers (2)

Ikacho
Ikacho

Reputation: 75

There are many options. Here are two...

First:
It'll look like:

                                      // AGGREGATOR \\ 
SOURCE >> SOURCE QUALIFIER >> SORTER <<              >> JOINER >> TARGET
                                      \\============//

Connect all ports from Source Qualifier (SQ) to SORTER transformation (or sort in SQ itself) and define sorting Key for ‘Policy’ and ‘Rider’. After that split stream into two pipelines:


  1. - Connect ‘Policy’ and ‘Rider’ to FILTER transformation and filter records by ‘Rider’ = 0.
    - After that link ‘Policy’ (only) to AGGREGATOR and set Group By to ‘YES’ for ‘Policy’.
    - Add a new port with FIRST or MAX function for ‘Policy’ port. This is to remove duplicate ‘Policy’-es.
    - Indicate ‘Sorted Input’ in the AGGREGATOR properties.
    - After that link ‘Policy’ from AGR to JOINER as Master in Port tab.

    2.
    - Second stream, from SORTER, directly link to above JOINER (with aggregated ‘Policy’) as Detail.
    - Indicate ‘Sorted Input’ in the JOINER properties.
    - Set Join Type as ‘Normal Join’ and Join Condition as POLICY(master)=POLICY(detail) in JOINER properties.


... Target

Second option:
Just Override SQL in Source Qualifier...

WITH PLC as (        
select POLICY
from SRC_TBL
where RIDER=0)
select s.POLICY, s.RIDER, s.PLAN
from PLC p left JOIN SRC_TBL s on s.POLICY = p.POLICY;

may vary depend on your source table constructions...

Upvotes: 0

Bharadwaj Aryasomayajula
Bharadwaj Aryasomayajula

Reputation: 15

Take the same source in one more qualifier in same mapping, use a filter as Rider=0 to get list of unique policy numbers that has Rider=0, then use a joiner with your regular source on policy number. This should work.

Another method, sort your data based on policy and Rider, and use variable ports with condition similar to below.

v_validflag=IIF(v_policy_prev!=policy, IIF(Rider=0, 'valid','invalid'), v_validflag)
v_policy_prev=policy

Then filter valid records.

Upvotes: 1

Related Questions