4 Replies Latest reply on Oct 11, 2012 12:51 PM by nheinze

    How can I use a variable in  Power Center??

    New Member

         Hello everybody,

        I wanted to  explain how can I use a variable  in Power  Center.  I have to  compare this variable into another table into  different consult.

      For  instance

      variable_code  (this will be the variable)

      115211

      115320

      115413

      311110

      321113

       

      Select  min(code) from AnotherTable where 

      substr(AnotherTable.code,1,4)=substr(variable_code,1,4) and to_number(AnotherTable.code)>to_number(variable_code)

       

      If it is not null. It is  OK

       

      Else

       

      Select  codefrom AnotherTable where 

      AnotherTable.code=substr(variable_code,1,4)

       

      If It is not null it is Ok

       

      Else

       

      Select  codefrom AnotherTable where 

      AnotherTable.code=substr(variable_code,1,3)

       

      In this case I have to  obstein a value. After that compare the next value of the variable_code

       

       

      that is wy I need to know how can I declare a variable in INFORMATICA Power Center

        • 1. How can I use a variable in  Power Center??
          Guru

          I'm pretty sure I misunderstood your requirement, so please be patient with me here.

          What I understood is that you want to create variable values during a session run; you then have to compare these against one another. Or against a group of other values. But that's all I understood.

           

          Could you please provide a somewhat more complete example with sample data?

          And without the SQL-like pseudo code above? Every time I see such code I tend to close my eyes and hope that it will pass by when I open them again (although I very well know that this will not happen). Some numbers or strings would help me to understand what you mean here.

           

          In general it may be that a Dynamic Lookup might be helpful for you here, but again, I can only respond usefully if you please explain a bit more to me.

           

          Thanks, Nico

          • 2. How can I use a variable in  Power Center??
            Naveed Rahman Guru

            As Nico said , please provide more stats.

            • 3. How can I use a variable in  Power Center??
              New Member

              Thanks for Replay Nica and Naved

              I have some data groups one of them is for example {‘115511’,’115514’,’311210’,’714687’} they are codes and they are store in a relational table. In another table I need to search them one by one into another relational table.

              First value 115511 if this exists in the second table

              2ndtable

              Code        Value

              115511     0.0

              If value is =0 then I have to search in the same table but in a more low level. It means when the code is 4 digits

              Code         Value

              1155         1.0

               

              It this case is OK, because the value is different to 0. But I need to store the code ‘115511’ into a variable; in order to when it processes next code ‘115514’ does not consider the previous value.

              I solve it how Nico told me but I wonder how I can use a variable instead of a dynamic look_up.

               

              Thanks in advance.

              • 4. How can I use a variable in  Power Center??
                Guru

                I hope that this time I understood your sample right. Here is what I would try:

                Source the first table with the codes you mentioned above.

                Feed every single code into an EXP.

                Within this EXP, first perform an Unconnected Lookup with this code on the second table and save the result in a variable port (name this e.g. "v_LKP_1").

                If v_LKP_1 is 0.0, then perform another Unconnected Lookup on this second table, but this time with

                    SubStr( inputcode, 1, 4)

                as the Lookup value (in your sample above, "1155" instead of "115511") and save this as variable port v_LKP_2.

                If v_LKP_2 = 0.0, you can perform a third Unconnected Lookup with

                    SubStr( inputcode, 1, 3)

                as the Lookup value (in your sample, "115").

                Now you simply have to see which of these three lookup results is != 0.0 and assign this result to the output port:

                    Decode( TRUE, v_LKP_1 != 0.0, v_LKP_1,

                        v_LKP_2 != 0.0, v_LKP_2, v_LKP_3)

                 

                Regards,

                Nico