Generation of Surrogate key for Incremental Load

Six Stars

Generation of Surrogate key for Incremental Load

Hi,

I have a column in a table in table for which am generating a surrogate value by using Numeric.sequence("S1",1000,1). This works fine for the 1st time but for the second time, I want it to  take max value form the last generated surrogate value. Here I am taking the max of that column into a context variable and using in the tmap Numeric.sequence("S1",Max(Column_Name),1).

Here I am facing an issue. Context variable is not accepting the int value in it. I am getting error.Capture.PNG

Capture_1.PNG

 

Can you please help me solve this issue.

 

Thanks in advance

ShaX

Tags (1)

Accepted Solutions
Forteen Stars

Re: Generation of Surrogate key for Incremental Load

@ShaX ,can you just set a some value like 1 for that context,you will not get the warring. 

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.

View solution in original post


All Replies
Forteen Stars

Re: Generation of Surrogate key for Incremental Load

@ShaX ,in th pre job you need to take the max key form the target table and load to context using tcontextload.you can use that context value this way in downstream.

Numeric.sequence("S1",context.DIm_DW_CUSTOMER_ID,1)

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Forteen Stars

Re: Generation of Surrogate key for Incremental Load

Hi,

 

it absolutely not clear from the description - what you really do?

 

in any variants, current form:

Numeric.sequence("S1",Max(Column_Name),1)

is wrong

 

if you store max value (not seen - how) to context variable, it must be:

Numeric.sequence("S1",context.CONTEXT_VARIABLE_NAME,1)

no MAX(), context. - mandatory

 

 

add:

as the default value for context variable use 1, it resolve null value warning

-----------
Six Stars

Re: Generation of Surrogate key for Incremental Load

Hi Manohar, I am trying to do the same. but my question is : 1. If I declare variable as int am getting "Null value will be used for context parameter DIM_DW_CUSTOMER_ID: For input string: "context.DIM_DW_CUSTOMER_ID"" this error. 2. If I declare variable as String then am getting "string to int conversion error" as the column am populating is Int Regards Sharukh
Highlighted
Six Stars

Re: Generation of Surrogate key for Incremental Load

Hi Vapukov,
My mistake I wrote that
Actually Numeric.sequence("S1",context.DIM_DW_CUSTOMER_ID,1) is the one am using.
Forteen Stars

Re: Generation of Surrogate key for Incremental Load

@ShaX ,yes As part of initialization,if you have not specified then it will take as Null and when the prejob completed successfully that max value would be stored in memory for that context and would be used in downstream.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Forteen Stars

Re: Generation of Surrogate key for Incremental Load

change the initial context variable value to 1, type INT

 

in query use CASE for return 1 if it first iteration and max(column) for any other

store to context max(column) + 1

 

must work

-----------
Six Stars

Re: Generation of Surrogate key for Incremental Load

Hi,

I got the weird result, here my max value is 1900, so it has to start from 1901 instead it is starting from 1190.

Capture.PNG

I got the below error in talend. and I have only 25353 rows instead of 35353 rows

Capture_1.PNG

 

Forteen Stars

Re: Generation of Surrogate key for Incremental Load


@ShaX wrote:

Hi,

I got the weird result, here my max value is 1900, so it has to start from 1901 instead it is starting from 1190.

 

I got the below error in talend. and I have only 25353 rows instead of 35353 rows

 

 


 

 

 

it is not seen - what's wrong (it is work definitely) 

diffrerence for 10 000 - because database rollback all batch (10k default batch size in talend) with any error

 

 

-----------
Six Stars

Re: Generation of Surrogate key for Incremental Load

Hi @vapukov 

Can you please explain how to over come this. caz i tried many ways but I am not understanding where am doing it wrong.

I think the issue is with the Variable which I am declaring as Int.Capture.PNG

Am getting above error.

 

Regards

ShaX

 

Regards

ShaX

Six Stars

Re: Generation of Surrogate key for Incremental Load

Any help on this plzz.

Forteen Stars

Re: Generation of Surrogate key for Incremental Load

@ShaX ,can you just set a some value like 1 for that context,you will not get the warring. 

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.

View solution in original post

Five Stars

Re: Generation of Surrogate key for Incremental Load

Hi All,

 

I am implementing SCD on my table ,i am trying to generate surrogate id using numeric sequence. 

To do this at the start of the job I am taking max of value and storing it in a context variable "context.SOURCE_ID".This context I am using in the numeric sequence. I am getting this error

PFA image.

 

picturemessage_kd3ha2un.fde.png

Regards,

Sreelekha.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog