Skip navigation
Contact Us
766 Views 4 Replies Latest reply: Feb 24, 2013 11:09 PM by Manojkumar Venkatachalam RSS
VEERA REDDY Newbie 5 posts since
Aug 5, 2012
Currently Being Moderated

Oct 27, 2012 12:14 AM

Informatica_data_extract

Hi,

 

PSB data:

Source data: Policy History data:

To seperate policy detila ,i kept one row blank

 

POLICY_NO, STATUS_ID

------------------  -----------------

1001,S1

1001,S2

1001,S3

1001,S4

1001,S5

 

1002,S1

1002,S4

1002,S5

 

1003,S3

1003,S4

1003,S5

 

1004,S3

1004,S4

 

My requirement is:

Case1: If same policy has both 'S1' & 'S5' STATUS_IDs  then i need to extract only  'S1' only and omit 'S5'

Case2: If same policy has either S1 or S5 then extract as it is at source i.e here we will get either S1 or S5 not both.

 

Target should be like below:

 

 

POLICY_NO, STATUS_ID

------------------  ------------------

1001,S1

1001,S2

1001,S3

1001,S4

 

1002,S1

1002,S4

 

1003,S3

1003,S4

1003,S5

 

1004,S3

1004,S4

 

any idea please...

 

Thanks in advance..

 

Thanks,

Veera

  • Sandeep Ravi Newbie 1 posts since
    May 25, 2012
    Currently Being Moderated
    Oct 27, 2012 5:30 PM (in response to VEERA REDDY)
    Informatica_data_extract

    This can be done using the expression transformation and variables. You need to do a current and prev logic using variables within an expression.

     

    e.g

     

    in_Policy_ID 

    in_Status

    var_current_id                  = in_policy_id

    var_current_status           =  in_status                                           

    var_flag_integer               = IIF(var_current_id=var_prev_id and                                                                                      IN(var_current_status,'S1',S5') and

                                             var_prev_status='C' ,var_flag_integer+1,0)

    out_flag                          = IIF(var_flag_integer < 1,'Y','N')

    var_prev_status               = IIF(

    ( var_current_id=var_prev_id and NOT IN(var_current_status,'S1',S5'),'C') )

    or IN(var_current_status,'S1',S5'),'C','P')

    var_prev_id                     = var_current_id

     

    Add a filter to remove all N from the out flag..

     

    Just a pseudo code off the back of my mind . I haven't tested this. Let me know if this works..

     

    Thanks

    Sandy

  • Manojkumar Venkatachalam Novice 82 posts since
    Dec 18, 2012
    Currently Being Moderated
    Feb 14, 2013 4:42 AM (in response to VEERA REDDY)
    Informatica_data_extract

    Hi Veera ,

     

    Make it Sorted Input based on POLICY_NO , STATUS_ID in Src_Qualifier itself

     

    Create a Router Transformation with One Group :

     

    • GROUP 1 - STATUS_ID IN ( 'S1' , 'S5' )
    • DEFAULT GROUP

     

    Now , all rows from DEFAULT Group can be directed to Target.

     

    Create an Expression Transformation with following Ports :

     

    • POLICY_NO , STATUS_ID - Input / Output Ports from Router Transformation
    • V_SUM_STATUS_ID - IIF ( POLICY_NO = V_PREV_POLICY_NO , STATUS_ID
      || '+' || V_SUM_STATUS_ID , STATUS_ID )
    • V_PREV_POLICY_NO - POLICY_NO
    • O_SUM_STATUS_ID - V_SUM_STATUS_ID

     

    Create an Aggregator Transformation with following Ports :

     

    • POLICY_NO , O_SUM_STATUS_ID - Input / Output Ports from Expression Transformation
    • No Group by Port

     

    Create an Expression Transformation as follows :

     

    • POLICY_NO , O_SUM_STATUS_ID - Input / Output Ports from Aggregator Transformation
    • V_CHK_STATUS - SUBSTR ( O_SUM_STATUS_ID , 1 , INSTR ( O_SUM_STATUS_ID , '+'  ) -1 )
    • STATUS_ID - IIF ( V_CHK_STATUS = 'S1' , 'S1' , 'S5' )

     

    The Flow is like ,

     

    SRC_QUAL  -->  RTR  -->  EXP  -->  AGG  -->  EXP  --> TGT

                                |

                                | ( DEFAULT GRP )

                                |

                                -->  TGT

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points