Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

SMANE
6 - Meteoroid

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

7 REPLIES 7
LordNeilLord
15 - Aurora

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
6 - Meteoroid

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
10 - Fireball

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.

IncrementRecordIDFromOldMaxID.yxmd_.png

 

Hope this helps!  

 

~ Chad

dataprepu.com

SMANE
6 - Meteoroid

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
10 - Fireball

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
6 - Meteoroid

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
10 - Fireball

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.  

Labels