Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Date format
 Login/Join
 
<Umar Farook S>
posted
i have a column called entry date which captures daily date details like account.
i need to find the difference between todays date and yesterdays date amount details.
final amount=
amount with entry date of today - amount with entry date of yesterday

please help me out
 
Report This Post
Guru
posted Hide Post
Umar,

Sort by your date field then you can create the following:

COMPUTE FINAL_AMT/D12.2=AMOUNT - LAST AMOUNT;

Fernando


Prod WF 8.1.04, QA WF 8.2.03, Dev WF 8.2.03
 
Posts: 278 | Registered: October 10, 2006Report This Post
<Umar Farook S>
posted
ASSET_ID FULL_MARKET_VALUE ENTRY_DATE
44 -13,482,320.00 2006/11/30
44 -13,521,531.25 2006/12/1
45 -205,000.00 2006/11/30
45 -205,390.63 2006/12/1

see here FULL_MARKET_VALUE has to be subtracted
based on the entrydate for each assetid.
we should have only one value for FULL_MARKET_VALUE after calculation
 
Report This Post
Expert
posted Hide Post
Umar,

Fernando gave you the idea towards your solution but you have to at least try to apply it.

Also, and this applies to all, please update your signature with your release, platform and usage so we have a good idea on what to advise. Kathleen has raised a post on this and it has been mentioned many times in the last few days, so help us to help you by complying.

Try this -
TABLE FILE filename
PRINT ENTRY_DATE
      FULL_MARKET_VALUE
      COMPUTE CURR_MARKET_VALUE/D20.2 = IF ASSET_ID EQ LAST ASSET_ID
               THEN FULL_MARKET_VALUE - LAST FULL_MARKET_VALUE ELSE FULL_MARKET_VALUE;
BY ASSET_ID
END

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
<Umar Farook S>
posted
Well that works fine Tony but
if there are many rows with same asset_id since entry dates are daily dates??
user selects a entry date and final market value should be calculated by subtracting the market value of the selected entry date with previous entry date that exists in the table for a particular asset_id.

44 -13,482,320.00 2006/11/30
44 -13,521,531.25 2006/12/1
44 -13,521,438.75 2006/12/3
44 -13,521,135.25 2006/12/4
45 -205,000.00 2006/11/30
45 -205,390.63 2006/12/1
45 -205,390.56 2006/12/3
45 -205,390.78 2006/12/4

if user selects entry date as 2006/12/3 final market value=[(-13,521,438.75)-(13,521,531.25)]
final market value=
[(market value as on selected date(i.e.2006/12/3))-(market value as on previous date 2006/12/1)]

if no previous date display the same market value
 
Report This Post
Virtuoso
posted Hide Post
Umar

-DEFAULT &ENTRYDATE='2006/12/03';
TABLE FILE filename
SUM FULL_MARKET_VALUE
BY HIGHEST 2 ENTRY_DATE
BY ASSET_ID
WHERE ENTRY_DATE LE '&ENTRYDATE'
ON TABLE HOLD

TABLE FILE HOLD
PRINT ENTRY_DATE
      FULL_MARKET_VALUE
      COMPUTE CURR_MARKET_VALUE/D20.2 = IF ASSET_ID EQ LAST ASSET_ID
               THEN FULL_MARKET_VALUE - LAST FULL_MARKET_VALUE ELSE FULL_MARKET_VALUE;
BY ASSET_ID
END 


This will give you the only the data from the selected date and the previous date.

I hope however that the date field in the database is indexed, since otherwise this report can take a long time.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<Umar Farook S>
posted
PLEASE HAVE ALOOK AT MY CODE BELOW..BUT I HAVE GIVEN THE ERROR AT LAST

-SET &ECHO = ALL;
-SET ASNAMES=ON
-DEFAULT &OracleConnect='ora022';
-DEFAULT &STARTDATE='11/30/2006';

SQL SQLORA SET SERVER &OracleConnect
SQL SQLORA
SELECT
ASSET_ID,
FULL_MARKET_VALUE,
ENTRY_DATE
FROM MurexODS.ACCOUNTING_BALANCES
-*WHERE ENTRY_DATE = TO_DATE('&STARTDATE','MM/DD/YYYY')
;

TABLE FILE SQLOUT
SUM FULL_MARKET_VALUE
BY HIGHEST 2 ENTRY_DATE
BY ASSET_ID
WHERE ENTRY_DATE LE '&STARTDATE'
ON TABLE HOLD
END
-RUN

TABLE FILE HOLD
PRINT ENTRY_DATE
FULL_MARKET_VALUE
COMPUTE CURR_MARKET_VALUE/D20.2 = IF ASSET_ID EQ LAST ASSET_ID
THEN FULL_MARKET_VALUE - LAST FULL_MARKET_VALUE ELSE FULL_MARKET_VALUE;
BY ASSET_ID
ON TABLE HOLD AS NEWFILENAME
END
-RUN
-*
TABLE FILE NEWFILENAME
PRINT *
END
-EXIT

::::ERROR GOT::::

0 ERROR AT OR NEAR LINE 6 IN PROCEDURE SQLOUT FOCEXEC *
(FOC177) INVALID DATE CONSTANT:
(FOC009) INCOMPLETE REQUEST STATEMENT
TABLE FILE HOLD
PRINT ENTRY_DATE
FULL_MARKET_VALUE
COMPUTE CURR_MARKET_VALUE/D20.2 = IF ASSET_ID EQ LAST ASSET_ID
THEN FULL_MARKET_VALUE - LAST FULL_MARKET_VALUE ELSE FULL_MARKET_VALUE;
BY ASSET_ID
ON TABLE HOLD AS NEWFILENAME
END
-RUN
0 ERROR AT OR NEAR LINE 59 IN PROCEDURE MEMFEX FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: ENTRY_DATE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
-*
TABLE FILE NEWFILENAME
PRINT *
END
-EXIT
0 ERROR AT OR NEAR LINE 77 IN PROCEDURE MEMFEX FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: NEWFILENAME
BYPASSING TO END OF COMMAND
 
Report This Post
Virtuoso
posted Hide Post
Umar

in the coding part

SQL SQLORA
SELECT
ASSET_ID,
FULL_MARKET_VALUE,
ENTRY_DATE
FROM MurexODS.ACCOUNTING_BALANCES
-*WHERE ENTRY_DATE = TO_DATE('&STARTDATE','MM/DD/YYYY')
;

The line starting with -* gives you the problem.
you are mixing webfocus scripting with sql
the comment in webfocus starts with -* but in SQL it should be "rem" I think.
Just place the ";" one line higher and it might work.

You can also create a master that describes the Oracle database. Than you do not have to write SQL scripting and let Focus do that job for you. Much easier IMO.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
Umar,

We use -* in our SQL sections of code all the time. This is one of the way we dynamically build all of our SQL. WF parses it out. I would look for a different issue.

Kevin


WF 7.6.10 / WIN-AIX
 
Posts: 141 | Location: Denver, CO | Registered: December 09, 2005Report This Post
<Umar Farook S>
posted
comment is not a problem that works fine

when i comment the below two statements i get the output with 2 rows

WHERE ENTRY_DATE = TO_DATE('&STARTDATE','MM/DD/YYYY')
WHERE ENTRY_DATE LE '&STARTDATE'

::::Red Faceutput::::::::
ASSET_ID ENTRY_DATE FULL_MARKET_VALUE ASSET_ID CURR_MARKET_VALUE
44 12/1/2006 -13,521,531.25 44 -13,521,531.25
44 11/30/2006 -13,482,320.00 44 -39,211.25
45 12/1/2006 -205,390.63 45 -205,390.63
45 11/30/2006 -205,000.00 45 -390.63
46 12/1/2006 -9,653,359.42 46 -9,653,359.42
46 11/30/2006 -9,635,000.00 46 -18,359.42
47 12/1/2006 -106,468.75 47 -106,468.75
47 11/30/2006 -106,160.00 47 -308.75

but instead of two rows i need only one row which contains the calculate(subtracted)Market_Value
 
Report This Post
Platinum Member
posted Hide Post
Also,

You may need to use &STARTDATE.EVAL, or do a;

 
(DATECVT(&STARTDATE , I8YYMD, YYMD)
 


to perform the date comparison.

Kevin


WF 7.6.10 / WIN-AIX
 
Posts: 141 | Location: Denver, CO | Registered: December 09, 2005Report This Post
Virtuoso
posted Hide Post
Just put the result again into a hold file. (HOLD2)

TABLE FILE HOLD2
PRINT *
BY HIGHEST 1 ENTRY_DATE NOPRINT
BY ASSET_ID
END




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<Umar Farook S>
posted
quote:
TABLE FILE HOLD2
PRINT *
BY HIGHEST 1 ENTRY_DATE NOPRINT
BY ASSET_ID
END


i get correctly when i change highest to lowest.
but i get an additional column in my output please refer below
:::CODE:::
TABLE FILE NEWFILENAME
PRINT CURR_MARKET_VALUE
BY LOWEST 1 ENTRY_DATE NOPRINT
BY ASSET_ID
ON TABLE HOLD AS NEWFILENAME1
END
-RUN

:::OUTPUT:::
ASSET_ID CURR_MARKET_VALUE RNK.ENTRY_DATE
44 -39,211.25 1
45 -390.63 1
46 -18,359.42 1
47 -308.75 1
48 -110,223.75 1

I DO NOT WANT THIS COLUMN TO APPEAR IN MY OUTPUT
 
Report This Post
Virtuoso
posted Hide Post
Umar

you seem to have two problems

1) is the number 1 in your report.

2) is the sorting highest and lowest.

The first problem has something to do with your date format.
A date format D/M/YY sorts in the wrong sequence.
As you will understand the first of december is a higher date than de last day of november.

Change your day format (internally) to either a real number (number of days since 1 jan 1900) or to the format YY/M/D.
Then you can use the BY HIGHEST 1 ENTRY_DATE

I have to think about your other problem.
maybe someone else know why the rnk.entry_date is printed.

If you leave out the "noprint" what do you get then, I suppose only the date.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
Umar

in stead of "by highest (or lowest) 1 entry_date"

you can say

TABLE FILE HOLD2
PRINT CURR_MARKET_VALUE
BY ASSET_ID
WHERE ENTRY_DATE EQ '&ENTRY_DATE';
END

The &Entry_date is the by the users entered date.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<Umar Farook S>
posted
when i executed the below code i got positve value.so interchanged the THEN statement to
THEN FULL_MARKET_VALUE - LASTFULL_MARKET_VALUE ELSE FULL_MARKET_VALUE;
i got CURR_MARKET_VALUE in negative which i wanted bcos FULL_MARKET_VALUE of today minus FULL_MARKET_VALUE of yesterday both are in negative

::CODE::
quote:
TABLE FILE HOLD
PRINT ENTRY_DATE
FULL_MARKET_VALUE
COMPUTE CURR_MARKET_VALUE/D20.2 = IF ASSET_ID EQ LAST ASSET_ID
THEN LASTFULL_MARKET_VALUE - FULL_MARKET_VALUE ELSE FULL_MARKET_VALUE;
BY ASSET_ID
ON TABLE HOLD AS NEWFILENAME
END
-RUN


so only i also changed the HIGHEST TO LOWEST in below code
TABLE FILE NEWFILENAME
PRINT CURR_MARKET_VALUE
BY LOWEST 1 ENTRY_DATE NOPRINT
BY ASSET_ID
ON TABLE HOLD AS NEWFILENAME1
END
-RUN

if you can suggest me a better way to change the date and try it plese do help me out
 
Report This Post
Virtuoso
posted Hide Post
Umar

I think this question is not solved, but you are getting more and more confused.

Let's start over and begin with posting your master on this table request.

I also wonder if the data you are using for this request is a basic table or a compound data report.

So start with posting the master file you want to use and then I can lead you step by step to the final result.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders