1 Reply Latest reply on Oct 11, 2019 12:45 PM by Nico Heinze

    Conditional loading

    Bipraraj Roy New Member

      HI All,

       

      I've got this scenario that i need to implement using power center 10.1.

       

      In my source table, there are home_phn, home_phn_base, work_phn, work_phn_base, mob_phn and mob_phn_base. Also 1 associated fields for each of these phn. Requirement is, if any of these phn is matching with its base counter part, then only that phn and its associated fields will be loaded in target1 and non-matching phns and its associated fields will be loaded in target_exception table for that single source record.

      Example,

      source(in a single record ) : home phn -> 11

      home phn field -> A

      home phn base -> 12

      work phn-> 22

      work phn field -> B

      work phn base-> 22

      mob phn->33

      mob phn field-> C

      mob phn base -> 34

      Target1:

      home phn-> NULL home phn field-> NULL Work Phn->22 Work phn field -> B mob phn-> NULL mob phn field-> NULL

      Target_exception:

      home phn-> 11 home phn field-> A Work Phn->NULL Work phn field -> NULL mob phn-> 33 mob phn field-> C

       

      Any advice would be very helpful. Thanks in advance.

        • 1. Re: Conditional loading
          Nico Heinze Guru

          If we were talking about filtering out complete records, a Filter transformation would be fine to use here.

          However, you want to have certain fields set to NULL under certain conditions.

          So all you can do is to use some Expression transformation. In this EXP, you will have to check all those combinations (such as WORK_PHN vs. WORK_PHN_BASE) and find out whether any of these checks fits your input record. If so, you will have to set the phone fields accordingly.

           

          In other words: you feed the EXP with the "raw" input data, and you forward "cleaned-up" phone numbers to the target tables.

          Only if none of the checks applies, you can filter out that record to tthe exception table: if there's nothing wrong with the input record, why should you write it to the exception table. So this is the only place (as of my understanding of your description) where you can apply a simple Filter transformation.

           

          Regards,

          Nico