I need to implement the below logic
Moderators: chulett, rschirm, roy
I need to implement the below logic
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.
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.
Kalpana Marupudi
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
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
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
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
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
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
Re: I need to implement the below logic
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
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
-
- Participant
- Posts: 36
- Joined: Mon Jun 28, 2010 11:24 pm
- Location: seepz
Re: I need to implement the below logic
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.
In the above condition can you please say what is #2 & #3 ,are they the job activity 2 &3.
NARESHKUMAR