How to retain the last incremented Record ID value for the incremental load

[SMANE]

Hi team,

I am using the Record ID to create the sequence generator, by setting the initial value. This we are using as a unique key for inserting records to table.

But the problem is each time the workflow runs the same Record ID value is creating, I want the value to be changed each time the workflow runs. means

if the workflow runs 1st time for 10 records then the last Record ID value is 10. when the second time same workflows runs for the different set of records

then Record ID should start from 11 onwwards.. 

Please guide me how to achieve this.

Regard

SMANE

[LordNeilLord]

Hey @SMANE

When the workflow runs again, how do you know that it's the second time of running? Is the last value of the recordID stored or re-inputted into the workflow?

[SMANE]

Hi LordNeilLord

Thanks for you reply, Presently I have put the Record ID initial value = 1 in the designer workflow,Which is used as unique key for inserting the records into the target table. this workflow will run as and when data needs to be loaded to target table. 

Each time we run the workflow ,we need the Record ID to be incremented not from the initial value set-up but from last run max record ID. Since I am new to Alteryx might not finding the right approach.

Need help on this :-)

[DataPrepChad · Accepted solution]

Check out the attached.  Personally, I'd first try to determine the max RecordID from the existing database, then append that to your new data stream.  If you create another new recordID that begins incrementing from 1, you then just simply add that to the max RecordID from the old set.

Hope this helps!  

~ Chad

dataprepu.com

[SMANE]

hi Chad,

Thanks for the solution, As in other tool eg. Informatica has the sequence generator, Which hold the last highest value of seq and next time you run the

workflow it automatically increments from the last value. I was thinking the same way in Alteryx too. But anyhow this works.

Thanks 

SMANE

[DataPrepChad]

Good to know @SMANE, one thing to keep in mind is the above workflow could easily be converted to a macro as well to make it easier to use in your designer instance. 

[SMANE]

Hi Chad

Thanks for the info. Now I am facing another challenge, The Record ID what i am generated from the max of some other lookup table has to be used in two

data flow. Means let say i have used the JOIN condition which splits data into two pipeline(Flow), 

One flow will write the data into TABLE_A, and other flow will will get some data from other table and also writes to TABLE_A. 

Now I want the unique record ID to be generated for both the flow as these are writing to the same target table.

Please see the attached Screenshot of the workflow. in that I have data coming from two source tables and i am joining them first. Next I am appending the MAX(Seq) from other table to flow one, it works fine, But i can't do the same MAX(Seq) appending to the second data flow. (high lighted in RED screen shot).

As that will regenerate the same record id of (max +1) to the second flow. Which I don't want

Please guide me this can be achieved in other way.

Appreciate you help on this 

Thanks

SMANE

[DataPrepChad]

Hi @SMANE, if I understand correctly, you should be able to take the max ID from one data stream (after processing) and append that to data stream 2 in a similar fashion, creating separate unique IDs that continue to increment.