Regading Timestamp..

[praburaj]

Hi,

I have one problem in my job. I have to compare source table and look up table based on key column using look up stage. If any mismatch records between these tables, then I have to store those records in particular column 'R' flag in RPRCS_C column.. If these records continiously retained in next 15 days, then I have change the flag value as 'E' in RPRCS_C column. I don't know how to do exactly. Can anyone help me out to resolve this issue?

[ray.wurlod]

Is there a "last updated date" column in the record as well? That would make the task very easy. Otherwise the solution is going to involve figuring out how to identify which "R" records have been there for your 14 day requirement. I can't really think of an elegant solution where the storage is in tables. Insist that "they" add a "last updated date" - or even "flag date" - to support their requirement.

[swapnilverma]

If any mismatch records between these tables, then I have to store those records in particular column 'R' flag in RPRCS_C column.. If these records continiously retained in next 15 days, then I have change the flag value as 'E' in RPRCS_C column.


This means all the record regardless matched or unmatched will go to the load files ? plz confirm
Also what is the frequency of your job ? is it daily or weekly or ..

[ray.wurlod]

How do you determine "15 days"? This is at the crux of the issue.

[John Smith]

One is that you have to store the date you loaded those records somewhere so you can work out 15 days. If you have that then the easiest way would be an UPDATE job to update the records to E.
Cau you modify the target table to contain a load date column?

[swapnilverma]

i dont think that you need to alter your table ....
what you need a re-circulation file out from lookupstage.

that will conatin all reject ( unmatched recs) ... this after adding a new column of date or counter ( in transformer) will be merged with input file in next cycle...
hence these recsords will be looked up again and if still a mismacth then increase the counter or load_date... if older then 15 days or 15 times ( if run cycle is daily) then modify the required column_value ...

hope that helps...

If you need more clarification ..then provide all the details ...

[John Smith]

as the saying goes "there's many ways to skin a cat". regardless there is still the fundamental issue of "15 days" which ray alluded to. you need to store that information somewhere whether it's in a file and merged back to your input file or a table. thanks for the idea though.it's good.

[bkumar103]

I have one problem in my job. I have to compare source table and look up table based on key column using look up stage. If any mismatch records between these tables, then I have to store those records in particular column 'R' flag in RPRCS_C column.. If these records continiously retained in next 15 days, then I have change the flag value as 'E' in RPRCS_C column. I don't know how to do exactly. Can anyone help me out to resolve this issue?

The design may ba as follow

input------>lookup------>Transformer-------->Lookup fail
| records to be inserted
|
|______> Records more then 15 days...
Needs to be updated in database


the first set will be inserted with the RPRCS_C value 'R'
the second set will be updated in the database with value 'E'
But it is required to figure out how to calculate the 15 days.

[bkumar103]

I have one problem in my job. I have to compare source table and look up table based on key column using look up stage. If any mismatch records between these tables, then I have to store those records in particular column 'R' flag in RPRCS_C column.. If these records continiously retained in next 15 days, then I have change the flag value as 'E' in RPRCS_C column. I don't know how to do exactly. Can anyone help me out to resolve this issue?

The design may ba as follow

input------>lookup------>Transformer -------->two link one for Lookup fail so the records to be inserted and another for Records more then 15 days... Needs to be updated in database


the first set will be inserted with the RPRCS_C value 'R'
the second set will be updated in the database with value 'E'
But it is required to figure out how to calculate the 15 days[/quote

[swapnilverma]

I have one problem in my job. I have to compare source table and look up table based on key column using look up stage. If any mismatch records between these tables, then I have to store those records in particular column 'R' flag in RPRCS_C column.. If these records continiously retained in next 15 days, then I have change the flag value as 'E' in RPRCS_C column. I don't know how to do exactly. Can anyone help me out to resolve this issue?

The design may ba as follow

input------>lookup------>Transformer -------->two link one for Lookup fail so the records to be inserted and another for Records more then 15 days... Needs to be updated in database


the first set will be inserted with the RPRCS_C value 'R'
the second set will be updated in the database with value 'E'
But it is required to figure out how to calculate the 15 days[/quote

If you talk about designe then below will do the desired ....


Input ---> lookup ----> Two links from lookup stage one for macthed recs and another for unmatched ( reject link) .
Reject link will goes to transformer and output link will goes to funnel .
Apply businnes logic in Transformer.
From Transformer again two links . One will go to funnel. and another will go to a re-circulation file.

From Funnel ---> final load file.


For merging recirculation file with next input file
This will be best
Use before job script to merge the re-circulation file with input file...

Or alternatively you can use a funnel in above design after input file and before lookup to merge ..