January 20, 2015, 07:23 PM
Darryl MarajHow to format for an across report?
So I don't know if I am going about this the wrong way but here's my scenario, looking for an idea of how to execute. I am thinking to somehow create a define that labels each row but then it would have to break based on the Part Number? Is that possible?
I have quotes for parts, my user wants an across of most recent four quotes listed by part number.
Data Received
Part Number, Quote Date, Quote Amount
Part A, 1/3/2015, 10
Part A, 1/2/2015, 11
Part A, 1/1/2015, 15
Part A, 12/31/2014, 12
Part A, 12/30/2014, 13
Part B, 1/3/2015, 20
Part B, 1/2/2015, 21
Part B, 1/1/2015, 25
Part B, 12/31/2014, 22
Part B, 12/30/2014, 23
Expected Result:
Part Number, Quote Amount by most recent four dates
----------------------------------------------------------------------------------------
Part Number | Date 1 | Price 1 | Date 2 | Price 2 | Date 3 | Price 3 | Date 4 | Price 4
----------------------------------------------------------------------------------------
Part A | 1/3/2015 | 10 | 1/2/2015 | 11 | 1/1/2015 | 15 | 12/31/2014 | 12
Part B | 1/3/2015 | 20 | 1/2/2015 | 21 | 1/1/2015 | 25 | 12/31/2014 | 22
January 20, 2015, 11:51 PM
Ram Prasad ECheck below example.
TABLE FILE EMPLOYEE
SUM
SALARY
BY DEPARTMENT
BY HIGHEST 2 HIRE_DATE
END
January 21, 2015, 07:12 AM
Tony AHi Darryl and welcome to the 'point
Whilst Ram has given you a starting point, what it doesn't cover is that you want these as an across. Neither does it hint at the problems that you will encounter if the last 4 quote dates for each individual quote are not the same.
Use Rams code as a base to sort your quotes by part by highest date, but add an additional COMPUTE to number the occurrences of the quote so that you can a) restrict the number of dates held and b) order them correctly in your subsequent ACROSS -
SUM QUOTE
COMPUTE Q_INDX/I4 = IF PART NE LAST PART THEN 1 ELSE Q_INDX + 1;
BY PART
BY HIGHEST DATE
WHERE TOTAL Q_INDX LE 4
ON TABLE HOLD AS TEMPHLD1
END
-RUN
TABLE FILE TEMPHLD1
SUM DATE
QUOTE
BY PART
ACROSS Q_INDX NOPRINT
.....
This will give you an idea of how you might code this type of request but may need more work on this basic example.
T
January 21, 2015, 08:40 AM
Darryl MarajThank you both for your input. What I was looking for is exactly what Tony A provided in this one line of compute code!
quote:
COMPUTE Q_INDX/I4 = IF PART NE LAST PART THEN 1 ELSE Q_INDX + 1;
You just saved my sanity and opened up so many other concepts in my mind, how can I buy you a coffee/tea to help you keep up the good work!!!
Suhweeeet!
January 21, 2015, 09:28 AM
Tony Aquote:
how can I buy you a coffee/tea
If we've saved your sanity then that is mission accomplished.
Of course, if you wish to increase my caffeine levels then you can always try and locate me at one of the Summits (not the US one this year though - just the UK).
T
January 21, 2015, 10:19 AM
Darryl MarajYou got it! The next time I'm in the UK for sure caffeine's on us!