I was looking for some type of tecnique for a data pull. Basically I'm reading a file to get what our current period is.
To get our period number I can go to this file, read it and subtract one.
SET ASNAMES = ON
-* File Build_E3history_2.fex
TABLE FILE E3COMP
BY LOWEST E3COMP.E3COMP.CPER13 AS 'CUR_CHECK'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SAVE AS CUR_CHECK
END
-RUN
-READ CUR_CHECK &CUR_CHECK.A3.
-*-SET &CUR_CHECK=1;
-SET &CUR_PERIOD=IF &CUR_CHECK EQ 1 THEN 13 ELSE (&CUR_CHECK - 1);
-TYPE &CUR_CHECK
-TYPE &CUR_PERIOD
The next step would be the actual data pull, our data looks like the following:
So if we are in period 1 of 2013, we would need period 13 and 12 of 2012 along with period 1 of 2013.
Our Final result would be something like:
ThisPeriod LastPeriod TwoPeriod
Item
WHSE
Thank you any help is appreciated. I'll also be banging my head against the wall trying to get this to work!
Thank you,
JCThis message has been edited. Last edited by: John C.,
WF 7703 Outputs all Windows 7 32 DB2 CLI
November 08, 2013, 03:15 AM
Dave
Been there, done that.
RELATIVE_PERIOD/I5 = ( YEAR * 13 ) + PERIOD;
This will create nice figure to work with.
P1 2013 will be = (2013*13)+1 = 26170 P13 2012 will be = (2012*13)+13 = 26169 P12 2012 will be = (2012*13)+12 = 26168
Now use your query to find out the 'cur_check'.
Today is P12 2013 = 26181 -SET &RELATIVE_CURCHECK = 26181;
Now query
TABLE FILE ...
SUM ...
BY RELATIVE_PERIODE
BY YEAR
BY PERIODE
WHERE RELATIVE_PERIODE LE &RELATIVE_CURCHECK
WHERE RELATIVE_PERIODE GE &RELATIVE_CURCHECK-2
END
Added bonus: You can use RELATIVE_PERIODE to sort the data if necessary...
Good luck, Dave
_____________________ WF: 8.0.0.9 > going 8.2.0.5
November 08, 2013, 03:54 AM
Wep5622
With something like this you wouldn't need to separately determine the current period:
TABLE FILE GGSALES
SUM MAX.DATE
SUM DOLLARS
BY HIGHEST 4 DATE
WHERE TOTAL MAX.DATE GT DATE;
END
This will automatically select the highest 3 dates in GGSALES that are before the latest date in that file. If you turn your periods + years into (chronological) date-like values - for example how Dave showed - you could use those to do the same.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
November 08, 2013, 08:52 AM
John C.
quote:
Originally posted by Dave: Been there, done that.
RELATIVE_PERIOD/I5 = ( YEAR * 13 ) + PERIOD;
This will create nice figure to work with.
P1 2013 will be = (2013*13)+1 = 26170 P13 2012 will be = (2012*13)+13 = 26169 P12 2012 will be = (2012*13)+12 = 26168
Now use your query to find out the 'cur_check'.
Today is P12 2013 = 26181 -SET &RELATIVE_CURCHECK = 26181;
Now query
TABLE FILE ...
SUM ...
BY RELATIVE_PERIODE
BY YEAR
BY PERIODE
WHERE RELATIVE_PERIODE LE &RELATIVE_CURCHECK
WHERE RELATIVE_PERIODE GE &RELATIVE_CURCHECK-2
END
Added bonus: You can use RELATIVE_PERIODE to sort the data if necessary...
Good luck, Dave
Dave,
Thank you for the great reply! With the data setup of:
TABLE FILE ... SUM P1_D AS 'L3-PERIOD' P2_D AS 'L2-PERIOD' P3_D AS 'L-PERIOD' BY ITEM BY WHSE END
If it is Period 2 it would be
TABLE FILE ... SUM P12_D AS 'L3-PERIOD' (2012 ONLY) P13_D AS 'L2-PERIOD' (2012 ONLY) P1_D AS 'L-PERIOD' (2013) BY ITEM BY WHSE END
I'm trying to understand how I translate my column P1 to the relative date? Do I use a define to map out the columns based on the relative date that I've created?
I understand this method if the periods were going down vs. accross. I think I need to dynamically select what columns to sum by year?
I'm trying to understand everything that I can to improve!
Thanks again!
JohnThis message has been edited. Last edited by: John C.,
WF 7703 Outputs all Windows 7 32 DB2 CLI
November 08, 2013, 09:32 AM
Dave
John,
you don't have to...
TABLE FILE ... SUM whatever BY ITEM BY WHSE ACROSS P_RELATIVE NOPRINT ACROSS P END
Show do fine...
the "ACROSS P_RELATIVE NOPRINT" is for sorting purposes only.
you could also do
ACROSS YEAR ACROSS P
to achieve that.
( If I understand correctly what you want )
_____________________ WF: 8.0.0.9 > going 8.2.0.5
November 10, 2013, 02:20 PM
Twanette
Hi,
Based on what the data looked like in your spreadsheet, I would suggest that you DEFINE the three fields.
There are probably various ways to do this.
The example below has a few "redundant" steps e.g. setting &SEL_PER1 and &SEL_YEAR1, but hopefully it gives you some ideas that you could use.
-SET &CUR_CHECK = '1' ;
-********
-SET &CUR_PERIOD = IF &CUR_CHECK EQ 1 THEN 13 ELSE (&CUR_CHECK - 1);
-* I don't know how you determine the year, so I've just hardcoded it for now
-SET &CUR_YEAR = '2013' ;
-* Determine the YEAR for Two_Period(3), Last_Period(2) and ThisPeriod(1)
-SET &SEL_YEAR3 = IF &CUR_PERIOD LE 2 THEN (&CUR_YEAR - 1) ELSE &CUR_YEAR ;
-SET &SEL_YEAR2 = IF &CUR_PERIOD EQ 1 THEN (&CUR_YEAR - 1) ELSE &CUR_YEAR ;
-SET &SEL_YEAR1 = &CUR_YEAR ;
-* Determine the PERIOD for Two_Period(3), Last_Period(2) and ThisPeriod(1)
-SET &SEL_PER3 = IF &CUR_PERIOD EQ 1 THEN 12 ELSE
- IF &CUR_PERIOD EQ 2 THEN 13 ELSE (&CUR_PERIOD - 1) ;
-SET &SEL_PER2 = IF &CUR_PERIOD EQ 1 THEN 13 ELSE (&CUR_PERIOD - 1) ;
-SET &SEL_PER1 = &CUR_PERIOD ;
-* DEFINE your three fields using the variables above
-* NB: If Year is an Alphanumeric format (e.g. A4), then use .QUOTEDSTRING e.g. &SEL_YEAR1.QUOTEDSTRING
-* The applicable period field will be determine when WebFOCUS substitutes the amper variables
-* e.g. if &SEL_PER1 = 1, then P&SEL_PER1|_DEMAND will result in the fieldname P1_DEMAND
DEFINE ...
ThisPeriod/I9 = IF Year EQ &SEL_YEAR1 THEN P&SEL_PER1|_DEMAND ELSE 0 ;
Last_Period/I9 = IF Year EQ &SEL_YEAR2 THEN P&SEL_PER2|_DEMAND ELSE 0 ;
Two_Period/I9 = IF Year EQ &SEL_YEAR3 THEN P&SEL_PER3|_DEMAND ELSE 0 ;
END
TABLE FILE ...
-* I'd suggest having a filter to limit the retrieval to at most two years
-* If the three periods all fall in the same year it won't be a problem
IF Year EQ &SEL_YEAR1 OR &SEL_YEAR3
-* You will need to SUM your fields, so that it the data spans two years, it still only shows one line
SUM
ThisPeriod
Last_Period
Two_Period
BY Item_ID
BY Warehouse_ID
END
WebFOCUS 8.2.06 mostly Windows Server
November 11, 2013, 08:38 AM
John C.
All,
We sat for a long time friday afternoon and hashed through this. Wanted to thank you all for your input. This is our period pull:
(I'm sure there are other JDA/E3 customers that use WebFOCUS, would love to share ideas).
We also built a weekly file, becuase as soon as we normalized this file, we are sure they will ask for weekly data vs. period.
Here is the 3 last period pull:
-SET &CUR_CHECK=3;
-SET &L1_PERIOD=IF &CUR_CHECK EQ 1 THEN 13 ELSE (&CUR_CHECK - 1);
-SET &L1_PERIOD_YEAR=IF (&CUR_CHECK EQ 1) THEN &LASTYEAR ELSE &CURRENTYEAR;
-SET &L1P='P'| &L1_PERIOD |'_DEMAND';
-SET &L2_PERIOD=IF &L1_PERIOD EQ 1 THEN 13 ELSE IF &L1_PERIOD EQ 2 THEN 1 ELSE (&L1_PERIOD - 1);
-SET &L2_PERIOD_YEAR=IF &L1_PERIOD EQ 1 THEN &LASTYEAR ELSE IF &L1_PERIOD EQ 13 THEN &LASTYEAR ELSE &CURRENTYEAR;
-SET &L2P='P'| &L2_PERIOD |'_DEMAND';
-SET &L3_PERIOD=IF &L2_PERIOD EQ 13 THEN 12 ELSE IF &L2_PERIOD EQ 12 THEN 11 ELSE IF &L2_PERIOD EQ 1 THEN 13 ELSE (&L2_PERIOD - 1);
-SET &L3_PERIOD_YEAR=IF &L2_PERIOD EQ 12 THEN &LASTYEAR ELSE IF &L2_PERIOD EQ 13 THEN &LASTYEAR ELSE IF &L2_PERIOD EQ 1 THEN &LASTYEAR ELSE &CURRENTYEAR;
-SET &L3P='P'| &L3_PERIOD |'_DEMAND';
-TYPE &L1_PERIOD
-TYPE &L1_PERIOD_YEAR
-TYPE &L2_PERIOD
-TYPE &L2_PERIOD_YEAR
-TYPE &L3_PERIOD
-TYPE &L3_PERIOD_YEAR
-TYPE &L1P
-TYPE &L2P
-TYPE &L3P
DEFINE FILE E3HISTORY
DEMAND1/D12=IF HAYEAR EQ &L1_PERIOD_YEAR THEN &L1P ELSE 0;
DEMAND2/D12=IF HAYEAR EQ &L2_PERIOD_YEAR THEN &L2P ELSE 0;
DEMAND3/D12=IF HAYEAR EQ &L3_PERIOD_YEAR THEN &L3P ELSE 0;
END
TABLE FILE E3HISTORY
SUM
E3HISTORY.SEG01.DEMAND1 AS 'DEMAND1'
E3HISTORY.SEG01.DEMAND2 AS 'DEMAND2'
E3HISTORY.SEG01.DEMAND3 AS 'DEMAND3'
COMPUTE DEMAND_MAX/D12=MAX(DEMAND1, DEMAND2, DEMAND3);
COMPUTE DEMAND_MIN/D12=MIN(DEMAND1, DEMAND2, DEMAND3);
COMPUTE USER_ID/A15='&USERID1';
COMPUTE PROG/A50='WF_Build_Forecast_Accuracy'; AS 'PROG_ID'
COMPUTE UPDATE_DATE/A8YYMD='&YYMD.EVAL'; AS 'UPDATE_DATE'
COMPUTE UPDATE_TIME/A8='&TIME';
BY E3HISTORY.SEG01.HAITEM
BY E3HISTORY.SEG01.HAWHSE
WHERE HAITEM EQ '2288210';
ON TABLE HOLD AS APP/E3_FORECAST_ACCURACY FORMAT DB2 TABLENAME DB2LIB/E3FCA
END
This message has been edited. Last edited by: John C.,