how get only new rows?

[saifnawaz]

Hi,
How can i extract only the new rows from my source since my last load to the target?
Thx

 

[ClaireHCM]

1.Define a mapping variable say $$Max_Date
2.In the Source Qualifier, define Source filter to be Date_Col > $$Max_Date
3.Let Date_Column go to Expression
4.In the Expression,add a variable port. Define as SETMAXVARIABLE($$Max_Date,Date_Col)
5.Drag the Date_Col output port from expression to target
6.Drag the rest of the ports from Source Qualifier to target.
7.Save it and run.

 

[smailyy]

You can use parameter file.

 

[bsellick]

Wanted add a couple things to ClaireHsu's posting.

Using version 7.x, I found that the port used for SETMAXVARIABLE() in an expression must be forwarded (outputted) to another transform otherwise it will not "fire". So what I mean in this example is, output the SETMAXVARIABLE port to another "dummy/useless" expression and also just forward the Date_col field to it, then connect Date_col to Target.

Another thing that got me when first using this. In order for the SETMAXVARIABLE() to fire, a row must be labelled as INSERT. So, this won't work if your session settings have something like "Treat rows as" Update. Must either be treat rows as INSERT or, I believe, mapping must have an update strategy transform and "Treat rows as" data driven (which then labels rows as INSERT by default up until you assess them in the update strategy).

Hope that is clear and helps. If we aren't talking about version 7, I can't say for sure if above applies.

- B