2 Replies Latest reply on Feb 3, 2021 2:03 AM by JanLeendert Wijkhuijs

    Help required in IF ELSE condition

    Saurabh Shrivastava Guru

      Hello All,

       

      In an expression transformation, I have 2 ports Ownarea and TargetArea

       

      I want to achieve below scenario

       

      Scenario 1:

      If Ownarea = 2560 or 0497 or 0498 then

      {

      If TargetArea contains any of the value in list (2801, 2804, 2836, 2839, 2843, 2844, 2873, 2884, 2878, 2393) then set the TargetArea to 0497

      OR

      If TargetArea contains any of the value in list (2800, 2815, 2825, 2842, 2850) then set the TargetArea to 0498

      }

       

      Scenario 2:

      If Ownarea = 0599 or 03** or 47**  (where ** is the Wildcard and can have any numeric value) then

      {

      If TargetArea contains any of the value in list (2801, 2804, 2836, 2839, 2843, 2844, 2873, 2884, 2878, 2393) then set the TargetArea to 0301

      OR

      If TargetArea contains any of the value in list (2800, 2815, 2825, 2842, 2850) then set the TargetArea to 0300

      }

       

      Both OwnArea and Target Area are of String Datatype

      Can someone please suggest a suitable approach to achieve this ??

       

      TIA

        • 1. Re: Help required in IF ELSE condition
          Nico Heinze Guru

          I don't understand what shall happen if neither of these conditions holds true. Shall the TargetArea remain unchanged in this case?

           

          Assuming my understanding is correct, I would use something like this:

           

          First, instead of passing through TargetArea, I would "split up" this port into two ports:

          - one input-only port i_TargetArea,

          - one output-only port o_TargetArea.

          Next step is to define a suitable expression term for o_TargetArea. The trick here is that "DECODE( TRUE..." can be used for kind of an "IF... ELSE IF ... ELSE IF..." construct.

           

          Decode( TRUE,

              In( OwnArea, '2560', '0497', '0498'),

                  Decode( TRUE,

                      In( i_TargetArea, '2801', '2804', '2836', '2839', '2843', '2844', '2873', '2884', '2878', '2393'), '0497',

                      In( i_TargetArea, '2800', '2815', '2825', '2842', '2850'), '0498',

                      i_TargetArea

                  ),

              OwnArea = '0599' OR In( SubStr( OwnArea, 1, 2), '03', '47'),

                  Decode( TRUE,

                      In( i_TargetArea, '2801', '2804', '2836', '2839', '2843', '2844', '2873', '2884', '2878', '2393'), '0301',

                      In( i_TargetArea, '2800', '2815', '2825', '2842', '2850'), '0300',

                      i_TargetArea

                  ),

              i_TargetArea

          )

           

          However, honestly I wouldn't implement it exacftly this way. What if any of the lists changes? Then you will have to change this expression term each time any of the value lists changes. Not really practical.

          For this reason I would set up an auxiliary table consisting of a "group indicator" (in your case, values would be 1 for OwnArea = 2560 or 0497 or 0498 resp. 2 for OwnArea = 0599 or 03* or 47*) and the individual target area numbers. Then you can perform a lookup on this auxiliary table with the "group indicator" (set via a DECODE() function similar to the one above but simpler) and the input target area; if this LKP finds something, then you would set the output target area to 0497, 0498, 0301, or 0300; otherwise take the target area as is.

           

          The big advantages of this auxiliary table are:

          First you can simply have it maintained by business people, and you don't have to care for its contents.

          Second this approach is more flexible than hard-coding the whole DECODE() stuff like above.

           

          Regards,

          Nico

          1 of 1 people found this helpful
          • 2. Re: Help required in IF ELSE condition
            JanLeendert Wijkhuijs Seasoned Veteran

            Hi,

             

            This isn't that hard to do.
            I interpreted the OR in both scenario's as ELSE.

            There are of course multiple ways of achieving this in PowerCenter.
            I assume you have 2 input ports for this (in_Ownarea and in_Targetarea).
            I would add 2 variable ports to an expression transformation and (at least) one output port (out_Targetarea).

            First variable port would be var_Ownarea (integer) with the following logic.

               IIF(IN(LTRIM(RTRIM(in_Ownarea)),'2560','0497','0498'),1,

                 IIF(LTRIM(RTRIM(in_Ownarea))='0599' or IN(SUBSTR(LTRIM(RTRIM(in_Ownarea)),1,2),'03','47'),0,-1

                 )

               )

             

            The second variable port would be var_Targetarea (integer) with the following logic.

               IIF(IN(LTRIM(RTRIM(in_Targetarea)),'2801','2804','2836','2839','2843','2844','2873','2884','2878','2393'),1,

                 IIF(IN(LTRIM(RTRIM(in_Targetarea)),'2800','2815','2825','2842','2850'),0,-1

                 )

               )

             

            Finally the logic for the out_Targetarea would become something like

               IIF(var_Ownarea = 1,(DECODE(var_Targetarea,1,'0497',0,'0498',??)),

                 IIF(var_Ownarea = 0,(DECODE(var_Targetarea,1,'0301',0,'0300',??)),??

                 )

               )

             

            On the questionmarks (??) you need to add a default value to fill in for the -1 values of the variable ports.

             

            Hope this helps

             

            Regards,
            JanLeendert

            1 of 1 people found this helpful