3 Replies Latest reply on May 2, 2021 8:50 PM by JT Mozingo

    Auto Increment Variables in IDQ Mappings

    JT Mozingo New Member

      Hey all!

       

      I have been trying to find a good way to do this in the IDQ Developer Tool, but just cannot for the life of me figure it out. In C# or any other language the solution would be trivial, so I assume it must be here as well and that I am just missing it. Basically what I need to do is take records which are output by a Reader transformation and assign those records IDs. Those records (with their new IDs) will then be pushed to an Web Service Transformation. I have looked into the Key Generator Transformation, but that appears to require a hardcoded starting value. The starting value that I have will need to be assigned at runtime by a SQL query to another database. This query will return the starting value for the IDs.

       

      Ex.

       

      3 records come out of the reader with no IDs.

      SQL query runs are returns a starting ID of 1234.

      3 records are then assigned their respective IDs.

           Record 1 == 1235 (1234 + 1)

           Record 2 == 1236

           Record 3 == 1237

       

      In a regular programming language I would simply set a for loop and assign the values as needed, but like I said, I can't find a good way to accomplish this task in the IDQ developer tool.

       

      Cheers,

      JT

        • 1. Re: Auto Increment Variables in IDQ Mappings
          Nico Heinze Guru

          As far as I know, there's indeed no out-of-the-box solution for your question,

          As I'm a PowerCenter-only guy, the following suggestion may be difficult to implement in IDQ, I don't know. So please bear with me if not, then we can still change the suggestion slightly to get what you need.

           

          In PowerCenter I would set up a mapping with two different "target load paths" (in fact two mapping streams which are not connected to each other).

           

          In the first target load path, I would issue the SQL query and get the last used starting value for the "sequence". Save this "last used sequence number" to some flat file or some DB table, that doesn't matter here.

           

          In the second target load path, retrieve the source records as usual (source definition or LDO plus source qualifier).

          Forward these records to some Expression transformation.

          Before setting up the following Expression transformation, insert an unconnected(!) Lookup on the intermediate storage which has been written to by the first target load path (the file or DB table holding the last used sequence value). This Lookup must somehow deliver this last used sequence number, no matter what the input is (there is an easy trick to achieve that, I'll explain in an extra post if you need this explanation). Let's assume you name this Lookup transformation LKP_PREV_SEQ.

          In this Expression transformation (be careful to disable optimisations for this EXP, that's important!), set up two variable ports of type Bigint, the first one named v_next_seq, the second one (set it below the first one!) named v_is_seq_retrieved.

          Set v_is_seq_retrieved to this expression:

              1

          Now set v_next_seq to this expression:

              IIF( v_is_seq_retrieved, v_next_seq, :LKP.LKP_PREV_SEQ( ... )) + 1

           

          Make sure that instead of the three dots in the IIF call you set up whatever value is needed for the unconnected lookup transformation.

           

          Probably you will have loads of questions. Please don't hesitate to ask them here.

           

          Regards,

          Nico

          • 2. Re: Auto Increment Variables in IDQ Mappings
            Meghana Botu New Member

            Hi JT,

             

            If your queries have been answered, can you please close the discussion & mark the answer has helpful. If not, please let us know if any further queries that you have?

             

            Thanks.

            • 3. Re: Auto Increment Variables in IDQ Mappings
              JT Mozingo New Member

              Hey there!

               

              Thanks for the suggestion. To be totally honest I haven't gotten to try this solution, but was poking around with it a bit tonight and found that I could use a Sequence Generator set to 1 and to rest after each run and then push through my records and increment my starting value by whatever value was in the Sequence Generator at that time. I think when I was originally trying to develop it I was too focused on being cute with and getting it all in one expression or something like that. This solution ended up working for our use case.

               

              Cheers,

              JT