|
View previous topic :: View next topic
|
| Author |
Message |
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Team,
I need a advise / best optimized solution to pick up and map the date as expected below .
File- F1: - Data pulled from our source table and way it has below date mapping on sales info.
| Code: |
---------------------------------------------------------------------------
DEPT-NBR THIS-YR-SALE-DATE LAST-YR SALE-DATE THIS-YR-SALES LAST-YR-SALES
---------------------------------------------------------------------------
1000 12/23/2019 12/24/2018 100 200
1000 12/24/2019 12/25/2018 200 150
1000 12/25/2019 12/26/2018 800 1000
1000 12/26/2019 12/27/2018 600 500
|
F2: Seperate input file on date to be compared on special dates with respect to the sales-amount calculation.
| Code: |
--------------------------------------------------
12/25/2019 12/25/2018
--------------------------------------------------
|
Output as expected :-
| Code: |
Output as expected :-
---------------------------------------------------------------------------
DEPT-NBR THIS-YR-SALE-DATE LAST-YR SALE-DATE THIS-YR-SALES LAST-YR-SALES
---------------------------------------------------------------------------
1000 12/23/2019 12/24/2018 100 200
1000 12/24/2019 12/25/2018 200 150
1000 12/25/2019 12/25/2018 800 150 <---------
1000 12/26/2019 12/27/2018 600 500
|
As per my knowledge , below is my thought to acheive this .
Join F1 & F2 on This-Year-date and populate date to be compared on file F1 as like below to form file - f3 on last column..
| Code: |
-------------------------------------------------------------------------------------
DEPT-NBR THIS-YR-SALE-DATE LAST-YR SALE-DATE THIS-YR-SALES LAST-YR-SALES SPL-LY-DATE
-------------------------------------------------------------------------------------
1000 12/23/2019 12/24/2018 100 200 12/24/2018
1000 12/24/2019 12/25/2018 200 150 12/25/2018
1000 12/25/2019 12/26/2018 800 1000 12/25/2018
1000 12/26/2019 12/27/2018 600 500 12/27/2018
|
FROM F3 form/build the file F4 to pull the columns as like below
| Code: |
F4:-
-------------------------------------------------------------
DEPT-NBR LAST-YR-DATE LAST-YR-SALES
-------------------------------------------------------------
1000 12/24/2018 200
1000 12/25/2018 150
1000 12/26/2018 1000
1000 12/27/2018 500
|
Join F3 & F4 on Last-year-Date and populate the last year sales on F3 for matching and for non-match retain the as it is.
Please advise is ther any optimized way that we achieve it quite easily.
Thanks
Balaji K |
|
| Back to top |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
What exactly are you trying to achieve with the date file?
You could JOIN both the inputs on LAST YR and write matched records to create something like this:
| Code: |
THIS YR LAST YR DEPT LAST-YR-SALES
--------------------------------------------------
12/25/2019 12/25/2018 1000 150
-------------------------------------------------- |
Then match this with the original input on DEPT, THIS YR (a JOIN UNPAIRED,F1
- F1 being your original input) to get the final result. |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| Arun Raj wrote: |
What exactly are you trying to achieve with the date file?
You could JOIN both the inputs on LAST YR and write matched records to create something like this:
| Code: |
THIS YR LAST YR DEPT LAST-YR-SALES
--------------------------------------------------
12/25/2019 12/25/2018 1000 150
-------------------------------------------------- |
Then match this with the original input on DEPT, THIS YR (a JOIN UNPAIRED,F1
- F1 being your original input) to get the final result. |
Thanks Arun - I will try this , yes this is what i need. Thanks for reducing the swap process in this . |
|
| Back to top |
|
|
|
|
