3 Replies Latest reply on Jan 21, 2022 5:35 AM by Jharana Patra

    Is it possible to use the parameter file to input the fields of a group by of an aggregation?

    Pedro Rodriguez Active Member

      Good morning,

       

       

      I am trying to parameterize the values of a group by and a joiner inside a mapping. The problem is that it forces me to put the values in the mapping task, but I need to report the values through a parameter file. is this possible?

       

      Thank you very much.

        • 1. Re: Is it possible to use the parameter file to input the fields of a group by of an aggregation?
          Prakash Marimuthu Active Member

          Hi Pedro

           

          May be the below steps can provide you some insights-

          1) Please write a dynamic shell script to update your parameter values on every run.May be below command can be used

           

          Example :

          Lets assume the parameter file test.parm contains the below value

          $Dbname = test
          After running the below command

          sed 's/\($Dbname=\).*/\1NewValue/' test.parm

          $Dbname =NewValue

           

          2) Call the above script in the pre-session command. Either you can pass the NewValue as run time parameter or can be read from another dynamic source file.

           

          3) Once the pre-session completes, the NewValue will be passed to your mapping task using the parameter file. Please refer the below link for how to call the parameters in various transformations.

          Input parameters

          • 2. Re: Is it possible to use the parameter file to input the fields of a group by of an aggregation?
            Pedro Rodriguez Active Member

            Good morning,

             

            thank you very much for the answer. Currently I already have a script that generates the parameter file dynamically before executing the taskflow (for project reasons I can't include shell script calls inside the taskflow).

             

            What I don't know is how I can inform the mapping task for which fields it should do the group by in each execution. That is to say, if I parameterize the fields of the "group by", in the mapping task it forces me to define which fields have to form this "group by". I cannot find the option to assign, for example, a string parameter.

            How can I solve this?

            That is to say, it can be valid in the case of doing the group by inside a query inside a source but, what if the group by is, as in this case, inside an aggregation box?

             

             

             

            Thank you very much.

             

            Best regards.

            • 3. Re: Is it possible to use the parameter file to input the fields of a group by of an aggregation?
              Jharana Patra Guru

              Hi Pedro,

               

               

              You can use an in-out parameter as an expression variable in the following transformations:
              • Expression
              • Filter
              • Aggregator
              • Router

               

              You can override the parameter at runtime with a value specified in a parameter file.

              1.  

                In the mapping, create an in-out parameter.

                 

              2. Configure the parameter properties.

              3. Set the data type to String or Text.

              4. Enable the

                Is expression variable option.

                 

              5. Use the parameter as an expression.

              6. Optionally, you can override the default value of the parameter in one of the following places:

                • On the
                  In-Out Parameters tab of the task.

                   

                • In a parameter file. Enter the parameter file name and location on the

                  Schedule tab of the task.

                   

              When the task runs,

              Data Integration

               

              resolves the parameter as an expression.

               

               

               

              -> Else you can use the group by query as a source query and use parameter file instead as an workaround.

              Please check if that helps .

               

              Overriding source queries

              In case you want to have it as a feature , please open a ticket with support and request for the same.