I need to implement the below logic

[kalpanam]

Hi All,

I Need to implement below logic,Please help me out.I have the SOURCE and REFERENCE tables as below:

SOURCE

DATE Store Sku
1-Dec-10 A 1
2-Dec-10 B 1
3-Dec-10 C 1
3-Dec-10 B 2
3-Dec-10 B 1

REFERENCE

DATE Store Sku Price
1-Dec-10 A 1 10
3-Dec-10 A 1 7
5-Dec-10 A 1 10
1-Dec-10 A 2 12
1-Dec-10 B 1 5
3-Dec-10 B 1 6
1-Dec-10 B 2 8
1-Dec-10 C 1 15

For every record in the SOURCE I have to get the PRICE from Refrence.

If DATE,STORE,PRICE matches we need to retrieve the PRICE value from the Reference else
I need to decrement the DATE value till found the exact match for that particular DATE,STORE & SKU

For example:

For Ist Record in the SOURCE(1-Dec-10 A 1) we have exact match in the reference so will get the PRICE value(10)

For 2nd Record in the SOURCE(2-Dec-10 B 1) I don't have exact match in the Reference so I will decrement the DATE by 1 then 2-Dec-10 becomes 1-Dec-10.Now I have to look for 1-Dec-10 B 1 in the Reference,now I will find the exact match with PRICE 5.

For 3rd Record in the SOURCE(3-Dec-10 C 1) I don't have exact match in the Reference so I will decrement the DATE by 1 then 3-Dec-10 becomes 2-Dec-10.Now I will look for 2-Dec-10 C 1 in the Reference I don't find the exact match in the Reference.So again I need to decrement the DATE by 1 then 2-Dec-10 becomes 1-Dec-10.Now I will look for 1-Dec-10 C 1 combination match in the Reference,now I will find the exact match with PRICE 15.

Thanks & Regards,
Kalpana.

[Ravi.K]

Is there any limit to check back dates or until match is found we need to check for back dates.

[kalpanam]

Is there any limit to check back dates or until match is found we need to check for back dates.

Thanks a lot for your quick response.

There is no limit in back dates checking.....we need to do back dates check until match is found.

Thanks & Regards,
Kalpana.

[ray.wurlod]

That could be an infinite loop. Please re-think the logic, or at least assure us that there will always be a reference date less than any date on the stream input, as a "catch all" case.

[kalpanam]

Hi Ray,

To the maximum we will have the refrence date less than any date on the input stream....

Thanks & Regards,
Kalpana.

[vinaymanchinila]

Ho about have 2 jobs and then looping it.

Jobs 1: read source file , do the look up and reject records that do not match.

Job2: Using exec command stage, rename the reject file as source file

Job3: Similar to job1 , but will take the new source file and minus 1 day and apply the look up again and reject if there is no match.


In the seq, keep job1 as the first activity and output should go to start loop.

Then Check the rej file , if you have records go ahead with #2 and #3 and end loop.

The loop will break when there are no records in the rej file.


HTH

[kalpanam]

Good Logic
Thanks For your response....

Could you please explain me the Job3 and how to check the Rej file whether it has 0 records(that means condition to break the loop).

Thanks & Regards,
Kalpana.

[vinaymanchinila]

Hi

To check the reject file, there are many ways, either you can use a execute command stage and have a unix command like cat and see if there is any output or do a link count.


For job3, read the new source file (the reject file that has been renamed) and send it to transformer, in the Xfm do the date -1, and then do the lookup and reject unfound records, for the records that do match, append to the target file created in Job1.

HTH

[edwardlee]

May be you can think in another way.

1) left join the source(LEFT) and reference with keys store and sku
2) filter out those records with soure_date < reference_date
3) get the max(reference_date)
4) join with reference data to get the price

[nareshketepalli]

Then Check the rej file , if you have records go ahead with #2 and #3 and end loop.

In the above condition can you please say what is #2 & #3 ,are they the job activity 2 &3.