 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point count occurrences based on values in a second table
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point count occurrences based on values in a second table Read-Only TopicGo | Search | Notify | Tools |
| count occurrences based on values in a second table | Login/Join |
| Platinum Member |
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 | ||
|
| Virtuoso |
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 | |||
|
| Platinum Member |
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 | |||
|
| Virtuoso |
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.
| |||||||
|
| Master |
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  | |||
|
| Platinum Member |
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. | |||
|
| Platinum Member |
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 | |||
|
| Virtuoso |
We all have that sometimes...it's nice not being the onlyone.
| |||||||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point count occurrences based on values in a second table
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point count occurrences based on values in a second tableCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |