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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     To separate -Ve values from define or compute field

Read-Only Read-Only Topic
Go
Search
Notify
Tools
To separate -Ve values from define or compute field
 Login/Join
 
Anish
Member
posted
Hi all

Can anyone please tell me how to separate -ve values or +ve values from a sum column/define/compute column.

i was trying by below wise

TABLE FILE TTTTT
SUM
TOT
BY ID AS 'ID'
BY Name AS 'Name'
HEADING
"Credit Balances Listing(All Students) As on"
FOOTING
"&DATEtrMDYY <+0> WHERE ( ENTRY_DATE GE DT(&EntryDate_From) ) AND ( ENTRY_DATE LE DT(&EntryDate_to) ) AND ( TOT LT 0 );

but when i was trying to do by that it is not separating the -ve values

Please help me out

Thanks
Regards
Anish


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
Well, I don't know what +ve and -ve are but the first thing I would recommend is that you change the name of the field TOT to something else. TOT would be considered 'reserved' as it is a WebFOCUS prefix operator.

So let us call it for purposes of discussion ID_TOTS.

So then where you have AND (TOT LT 0), change this to:

WHERE TOTAL ID_TOTS LT 0

See if that works.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Anish
Member
posted Hide Post
Ginny

Its not working by that way
Is there any other way to do?
It will be very helpful

Thanks
Anish


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
FrankDutch
Virtuoso
posted Hide Post
I suppose -ve and +ve are the negative and positive values?




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
GinnyJakes
Expert
posted Hide Post
Anish,

Can you do a PRINT on that TOT column and show us some of the values. Also, what is the format of the field in the master file description?

I think we are have trouble understanding the -ve and +ve stuff.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Anish
Member
posted Hide Post
Ginny

I am sorry
-ve ------ Negative
+ve ------- Positive

Thanks
Anish


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
jbond007
Silver Member
posted Hide Post
you're missing a (") at the end of your text in your footing statement.


7.61, nt
Output: excel, pdf, html,
 
Posts: 33 | Registered: January 10, 2008Report This Post
jbond007
Silver Member
posted Hide Post
try this

DEFINE FILE CAR
TOT/D13.2 = RCOST ;
END
TABLE FILE CAR
SUM TOT
BY COUNTRY
FOOTING
"THIS IS THE END


7.61, nt
Output: excel, pdf, html,
 
Posts: 33 | Registered: January 10, 2008Report This Post
Anish
Member
posted Hide Post
Hi all

I just want to write my query in clear way once again.

I have created one Define or compute field which contains negative values and positive values, so i want to show this define or compute field and also the new column with only negative values from the first define or compute field.

Please help me out

Thanks
Regards
Anish


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
Please give us the format of the TOT field. If it is alpha and not numeric, what you are currently doing will not work.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Anish
Member
posted Hide Post
Ginny,

It is not working

The tot format is D15.2

As i said above i have to separate the negative values but the first define field is summed
How i can check condition for summed column?

Thanks
Regards
Anish


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
Use a COMPUTE instead of a DEFINE. A COMPUTE is done on the internal matrix after all the data has been processed.

Or you can do a WHERE TOTAL test.

Also please change the name of the TOT field as I recommended above.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Anish
Member
posted Hide Post
Ginny

I have changed tot to Curr_tot.
i have used compute instead of define.
How to USE sum function in compute field?

Thanks
Regards
Anish


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
Anish,

You really need to get some training or read the manuals, especially "Creating Reports with the WebFOCUS Language. Some of this is really basic stuff. Reading the manual will give you an idea of what you can do with the product.

Try this.

 TABLE FILE TTTTT
SUM CURR_TOT
BY ID AS 'ID'
BY Name AS 'Name'
HEADING
"Credit Balances Listing(All Students) As on"
FOOTING
"&DATEtrMDYY <+0> 
WHERE ( ENTRY_DATE GE DT(&EntryDate_From) ) AND ( ENTRY_DATE LE DT(&EntryDate_to) );
WHERE TOTAL CURR_TOT LT 0 ; 
END


But to answer your question, if your verb is SUM, your COMPUTE will be summed. If your verb is PRINT, your COMPUTE will be printed.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Anish
Member
posted Hide Post
Ginny

I am sorry, but i already tried with that i am getting abnormal result.....Smiler

Even i will try once

Thanks
Anish


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
What abnormal result are you getting?

TABLE FILE CAR
SUM SALES 
BY COUNTRY
WHERE TOTAL SALES GT 50000
END

If you run this without the WHERE TOTAL, you get all the sales. With it, you only get two.

Isn't this what you are trying to do?


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Anish
Member
posted Hide Post
Ginny

Yes, i am trying with compute and wheretotal, and now i am doing data mining, whether i have any problems with the data.


Thanks
Regards
Anish


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
GamP
Virtuoso
posted Hide Post
If I understand you correctly, you want to have a column with the defined positive or negative value, and also a column with just the negative values. Is that correct?
If so then consider the next example code:
DEFINE FILE CAR
DEFFLD1/D8  = IF SEATS LT 4 THEN -DCOST ELSE RCOST;
DEFFLD2/D8S = IF DEFFLD1 GE 0 THEN 0 ELSE DEFFLD1;
END
TABLE FILE CAR
PRINT CAR MODEL SEATS DEFFLD1 DEFFLD2
BY COUNTRY
END
TABLE FILE CAR
SUM CAR MODEL SEATS DEFFLD1 DEFFLD2
BY COUNTRY
END

Would this be of any help to you?


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     To separate -Ve values from define or compute field

Copyright © 1996-2020 Information Builders