[CLOSED] Technique for Today vs. Yesterday. Data Stored in Rows
Morning All,
We have a strange problem, we are looking at our inventory changes today vs. yesterday, we have a .fex that will get us the two dates to a hold file, then insert that in list as a where statement on our second query.
This limits our results from a few million record table, but the data is stored like this...
TABLE FILE F568304
BY HIGHEST 2 JOBDATE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SAVE AS 'HOLD1' FORMAT ALPHA
END
We want to subtract by the different dates in the rows, this example just gives us the two values for the two days (we tried a few things, but didn't get it to work):
TABLE FILE F568304
SUM
NetChange
BY LOWEST JOBDATE
WHERE ( Q2DXJ IN FILE HOLD1 );
Appreciate any help, once we have a working example we can do this anywhere
*Edits- Cleaned up the example.
Thank you,
JCThis message has been edited. Last edited by: <Kathryn Henning>,
WF 7703 Outputs all Windows 7 32 DB2 CLI
April 19, 2013, 11:14 AM
Wep5622
In such cases we use MATCH FILE on the results for today and those for yesterday.
MATCH
FILE F568304
SUM NetChange AS NetChangeToday
WHERE JOBDATE EQ &Today;
RUN
FILE F568304
SUM NetChange AS NetChangeYesterday
WHERE JOBDATE EQ &Yesterday;
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
SUM Diff/D12.2 = NetChangeToday - NetChangeYesterday;
END
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 :
April 19, 2013, 11:35 AM
RSquared
We would do a define or compute /* DEFINE FILE F568304 NETTODAY/D20.2=IF JOBDATE EQ &TODAY THEN Netchange ELSE 0; NETYESTR/D20.2=IF JOBDATE EQ &YESTERDAY THEN Netchange ELSE 0; END TABLE FILE F568304 SUM NETTODAY NETYESTR COMPUTE NETCHANGE/D20.2= NETYESTR - NETTODAY;
WHERE JOBDATE EQ &TODAY OR &YESTERDAY END
WF 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit
April 19, 2013, 11:41 AM
John C.
If the 2 dates in HOLD1 changes everyday. How do I get them to &values.
Would I do a -READ? I've only used that in tables with 1 record, then used that one record as a where statement.
WF 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit
April 19, 2013, 12:43 PM
John C.
I've done something like that, but not like a century julian.
CYDDD (P7)
Would it be the same type of -set values?
WF 7703 Outputs all Windows 7 32 DB2 CLI
April 19, 2013, 01:43 PM
RSquared
As long as the dates you are comparing are in the same format, I don't see why not. Just try it and see what happens.
WF 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit
April 19, 2013, 02:21 PM
John C.
How do I populate &CURDATE? It's prompting for it.
WF 7703 Outputs all Windows 7 32 DB2 CLI
April 19, 2013, 02:43 PM
RSquared
i miised a change. It should be &today.
WF 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit
April 19, 2013, 03:02 PM
John C.
Code:
-SET &BASEDATE = &YYMD ;
-SET &TODAY = DATECVT (&BASEDATE,'I8YYMD','YYMD');
-SET &YESTERDAY = DATEADD(&TODAY, 'D', -1);
DEFINE FILE F568304
NETTODAY/D20.2=IF JOBDATEEQ &TODAY THEN NETCHANGE ELSE 0;
NETYESTR/D20.2=IF JOBDATEEQ &YESTERDAY THEN NETCHANGE ELSE 0;
END
TABLE FILE F568304
SUM NETTODAY NETYESTR
COMPUTE NETCHANGE/D20.2= NETYESTR - NETTODAY;
WHERE JOBDATEEQ &TODAY OR &YESTERDAY
END
FOCUSEXE:
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON
SET XRETRIEVAL = OFF
-SET &BASEDATE = 20130419 ;
-SET &TODAY = DATECVT (20130419,'I8YYMD','YYMD');
-SET &YESTERDAY = DATEADD(41017, 'D', -1);
DEFINE FILE F568304
NETTODAY/D20.2=IF JOBDATEEQ 41017 THEN NETCHANGE ELSE 0;
NETYESTR/D20.2=IF JOBDATEEQ 41016 THEN NETCHANGE ELSE 0;
END
TABLE FILE F568304
SUM NETTODAY NETYESTR
COMPUTE NETCHANGE/D20.2= NETYESTR - NETTODAY;
WHERE JOBDATEEQ 41017 OR 41016
END
SQL:
14.57.57 AE SELECT
14.57.57 AE SUM((CASE (T1."NETCHANGE") WHEN 41017 THEN T1."NETCHANGE" ELSE 0
14.57.57 AE END)),
14.57.57 AE SUM((CASE (T1."NETCHANGE") WHEN 41016 THEN T1."NETCHANGE" ELSE 0
14.57.57 AE END))
14.57.57 AE FROM
14.57.57 AE HSIQDTA71/F568304 T1
14.57.57 AE WHERE
14.57.57 AE (T1."JOBDATE" IN(41017, 41016))
14.57.57 AE FOR FETCH ONLY;
Edits- Made field names more freindly.This message has been edited. Last edited by: John C.,
WF 7703 Outputs all Windows 7 32 DB2 CLI
April 19, 2013, 03:07 PM
RSquared
John,
This works?
WF 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit
April 19, 2013, 03:11 PM
John C.
The date being sent in SQL doesn't match the CYDDD, so returns 0 records.