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     count occurrences based on values in a second table

Read-Only Read-Only Topic
Go
Search
Notify
Tools
count occurrences based on values in a second table
 Login/Join
 
Platinum Member
posted
Hi Everyone

I am stumped with a request and I am sure that somebody must have an answer for me.

I need to do something like the following, which is just for illustration.

TABLE A
Product POS_SOLD
apples $1.00
apples $1.25
pears $1.10


TABLE B
Product ACT_SOLD
apples $2.00
apples $1.00
pears $1.10
pears $1.10
pears $3.00

I need to create a report that shows how many times a product in Table B was sold at each value in TABLE A.

So in this example, I would need to produce these numbers..

We sold 2 apples. We used the $1.00 value 50% of the time and the $1.25 value 0% of the time.
We sold 3 pears. We used the $1.10 value 66% of the time.


The values in Table A are dynamic of course. So there could be 100 values per fruit or only 1.

For some reason I am having trouble wrapping my head around this one.

Thanks in advance.

Jodye
 
Posts: 246 | Location: Montreal, QC, Canada | Registered: October 01, 2003Report This Post
Virtuoso
posted Hide Post
In your limited example, I see know way to know how many as relates to price per each except that you state as much. Does your real data have that available. Such as 5 items cost 5 dollars so price per item could be then 1, of course what if you have 5 items at 5.75, does this mean 2 at 1 and 3 at 1.25?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Platinum Member
posted Hide Post
Hi Leah

Thanks for responding, but, I do not follow your question. Table B shows actual sales and Table A shows possible values.

I want to know how many actual sales were made for each fruit at each value in Table A. Table B shows that I sold 3 pears and 2 of them were $1.10 so that means I hit $1.10 2/3 (66%) of the time. I need to know how many sales were made at 1.10$ because that is in Table A.

Please let me know if there is something I can add to make it more clear.

EDIT: oh wait I see what you mean. For this example assume that each individual record in Table B represents one item. Pretty expensive fruit huh. So I can simply count the number of hits at each price.

Thanks

Jodye


WF 8.0.0.5M
 
Posts: 246 | Location: Montreal, QC, Canada | Registered: October 01, 2003Report This Post
Virtuoso
posted Hide Post
So your Master file description of TABLEA has two key fields (the productname and the price)
and every product_price from TABLE B is in the TABLE A.

The solution is rather easy

TABLE FILE TAB_A
COUNT COMPUTE RECORDS/I3=1;
BY PRODUCT
BY PRICE
END

You get a report like
product price count
apples  $1.00    1
apples  $2.00    1
pears   $1.10    2
pears   $3.00    1    


and you can calculate the percentage of that count too.

If the $2.00 apples and $3.00 pears are not in table A you can join the two tables with an inner join and then you only get the products from your table A counted.




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
Master
posted Hide Post
This is how I would do it.

1. Read table B and create a hold file with the total counts by product and by product by act_sold.

SET ASNAMES = ON

TABLE FILE B
SUM
CNT.PRODUCT/I9 AS 'TOTPRODUCTCNT'
BY PRODUCT
SUM
CNT.PRODUCT/I9 AS 'TOTACTSOLDCNT'
BY PRODUCT
BY ACT_SOLD
ON TABLE HOLD AS HOLD1
END

SET ALL = PASS

2. JOIN PRODUCT AND POS_SOLD IN TABLEA TO
PRODUCT AND ACT_SOLD IN HOLD1 AS JOIN1

3. Using a DEFINE FILE TABLEA create your percentage field.

4 Using a TABLE FILE TABLEA print your fields.

Hope this helps




Scott

 
Posts: 865 | Registered: May 24, 2004Report This Post
Platinum Member
posted Hide Post
SET ASNAMES = ON
SET BYDISPLAY = ON
FILEDEF act_sold DISK C:\ibi\apps\bw\act_sold.ftm
FILEDEF pos_sold DISK C:\ibi\apps\bw\pos_sold.ftm
TABLE FILE ACT_SOLD
COUNT PRODUCT AS TOT_PRODUCT
BY PRODUCT
COUNT PRODUCT AS ACT_PRODUCT
PRICE AS ACT_PRICE
BY PRODUCT
BY PRICE
ON TABLE HOLD AS HOLDACT
END
TABLE FILE POS_SOLD
PRINT PRODUCT AS POS_PRODUCT
PRICE AS POS_PRICE
BY PRODUCT
BY PRICE
ON TABLE HOLD AS HOLDPOS
END
MATCH FILE HOLDACT
PRINT TOT_PRODUCT
ACT_PRODUCT
ACT_PRICE
BY PRODUCT
BY PRICE
RUN
FILE HOLDPOS
PRINT POS_PRODUCT
POS_PRICE
BY PRODUCT
BY PRICE
ON MATCH HOLD NEW
END
TABLE FILE HOLD
PRINT TOT_PRODUCT
ACT_PRODUCT
COMPUTE PRODUCT_PCT/D7.2% = ACT_PRODUCT/TOT_PRODUCT;
BY PRODUCT
BY PRICE
END


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Report This Post
Platinum Member
posted Hide Post
Thanks guys. I got it to work. The real requirements are way more complicated than I let on, and they were throwing me for a loop and making me see complexity in a part of the report where there wasn't any. If that makes sense. :-)


WF 8.0.0.5M
 
Posts: 246 | Location: Montreal, QC, Canada | Registered: October 01, 2003Report This Post
Virtuoso
posted Hide Post
We all have that sometimes...it's nice not being the onlyone.




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

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     count occurrences based on values in a second table

Copyright © 1996-2020 Information Builders