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     Can't use LIST in DevStudio?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Can't use LIST in DevStudio?
 Login/Join
 
Member
posted
I need to get a list of reps who have 10 or more clients with matching transactions. The first step I was going to take is to create a "list" of clients and accounts by the fields that I need to match on by Rep, however I don't see how to LIST in DevStudio. Here's my code:
TABLE FILE AL_ACCOUNTTRANS
PRINT ---- I tried to replace PRINT with LIST
SSN_TIN
ACCT_NUMBER
BY
ADVISOR_TAX_ID
BY
TRANS_DATE
BY
SEC_TYPE
BY
BuySell
BY
NORM_TRANS_CODE
BY
FUND_NAME
BY
DESCRIPTION
WHERE TRANS_DATE GE WEEKDATE;
IF INT_TYPE EQ (INTTYPES)
IF NORM_TRANS_CODE EQ (NORMTRANS)
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON

The error I get when I try to run the code with LIST is: Error Parsing Report Request

Thanks for any help....
 
Posts: 4 | Location: Woodbury, MN | Registered: November 08, 2004Report This Post
Virtuoso
posted Hide Post
If I understand correctly, you want to list reps (advisor_tax_id) and their clients (acct_number, ssn_tin) where the rep has at least 10 distinct client accounts that had certain types of transactions in a given period of time (as per your IF and WHERE phrases).

You can do that in two steps:

-* 1. get one record per distinct combination of
-* advisor and client
TABLE FILE AL_ACCOUNTTRANS
WRITE CNT.TRANS_DATE
BY ADVISOR_TAX_ID
BY ACCT_NUMBER
BY SSN_TIN
WHERE TRANS_DATE GE WEEKDATE;
IF INT_TYPE EQ (INTTYPES)
IF NORM_TRANS_CODE EQ (NORMTRANS)
ON TABLE HOLD
END

-* 2. produce listing of advisors and their
-* clients, where client count is 10 or more
TABLE FILE HOLD
WRITE CNT.ACCT_NUMBER NOPRINT
BY ADVISOR_TAX_ID
LIST ACCT_NUMBER
SSN_TIN
BY ADVISOR_TAX_ID
IF TOTAL CNT.ACCT_NUMBER GE 10
END
You can use Dev Studio to style the second part as you please. You may want add Joins in step 2, to include other attributes for the reps and clients in the listing.

If you want to list the transactions by rep by client, hold the list of reps from step 2, and use that to screen on rep (advisor code) in the code you originally posted as step 3.

This message has been edited. Last edited by: <Mabel>,
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
Thanks. I think this is getting me closer. The client can only be counted once if he has more than one of the same type of transaction on a given day. The "match" on transcation is determined by TRANS_DATE, BUYSELL, NORM_TRANS_CODE, FUND_NAME AND DESCRIPTION. A client is determined by his SSN_TIN and ACCT_NUMBER combination.
 
Posts: 4 | Location: Woodbury, MN | Registered: November 08, 2004Report This Post
Virtuoso
posted Hide Post
This should do it:

-* get set of "distinct" transaction rows per rep
TABLE FILE AL_ACCOUNTTRANS
BY ADVISOR_TAX_ID
BY SSN_TIN
BY ACCT_NUMBER
BY TRANS_DATE
BY SEC_TYPE
BY BUYSELL
BY NORM_TRANS_CODE
BY FUND_NAME
BY DESCRIPTION
WHERE TRANS_DATE GE WEEKDATE;
IF INT_TYPE EQ (INTTYPES)
IF NORM_TRANS_CODE EQ (NORMTRANS)
ON TABLE HOLD AS HOLD1
END

-* get list of reps with 10+ rows
TABLE FILE HOLD1
COUNT ENTRIES NOPRINT
BY ADVISOR_TAX_ID
IF TOTAL COUNT GE 10
ON TABLE HOLD AS HOLD2 FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END
?FF HOLD2
...followed by your report, with

IF ADVISOR_TAX_ID EQ (HOLD2)

added to its selection criteria.

(The ?FF displays the fields and their formats for the hold file; HOLD2 should only have a single field, ADVISOR_TAX_ID.)

This message has been edited. Last edited by: <Mabel>,
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report 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     Can't use LIST in DevStudio?

Copyright © 1996-2020 Information Builders