 |
|||||
|
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. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| Data formatting change | Login/Join |
| Silver Member |
I have data in a comma separated file (extra spaces added for readability): Date, Branch 1, Branch 2, Branch 3 11/07/2007, 1315.23, 874.24, 878.00 12/07/2007, 1045.23, 1515.02, 989.11 13/07/2007, 1442.74, 989.00, 584.00 "effectivley SUM VALUE, BY Date, ACROSS Branch" The number of branches can vary and each branch identified by the branch number (unique). The data needs to be represented like this, for onward loading to a db table. Date Branch Value ---- ------ ----- 11/07/2007 1 1315.23 11/07/2007 2 874.24 11/07/2007 3 878.00 12/07/2007 1 1045.23 12/07/2007 2 1515.02 12/07/2007 3 989.11 13/07/2007 1 1442.74 13/07/2007 2 989.00 13/07/2007 3 584.00 "effectivley SUM VALUE, BY Date, BY Branch" Q. Can this be done with a Master File Description and 'Occurs', or are we looking at programatically flattening the data? Any help greatly received. Thanks, Paul * Win 2003, IIS, WF 763 Server, WF 762 Client, Report Caster, Active Reports, Report Library, CUS, Managed Reporting, Resource Governer | ||
|
| Expert |
I don't see the branch number in your file. Is it positional? For instance, what if there is no branch 1. Is branch 2 then in the first position? 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 | |||
|
| Silver Member |
The branch is in the header line of the source data, in reality the branch number is normally 4 digit integer. In the example I've just given Branch 1, Branch 2 etc.... | |||
|
| Expert |
Yes, but if there is no branch 1, is a space left for it to mark it as null such that branch 2 is always in slot 2? I know I'm being picky, but the answer will really help in giving you the correct solution. 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 | |||
|
| Silver Member |
Picky is good, apologies for not being detailed enough initially. In theory yes, the branch will always exist in the same position even with no data, but the variable element comes if a new branch is added. We could always ask the business to ensure the 'new' branch is added at the righ-hand-most column. But we're looking the most low-maintenance solution, changng the MFD is unaccepatble if a new branch is added. P | |||
|
| Virtuoso |
It can if you have allowed for non-existent data, as in Ginny's question. Leah | |||
|
| Expert |
I played around with COMT and OCCURS. Didn't work. If I used plain, old COMT and specified a field for each of the branch values, it handled the missing data properly. It did not use the headings in row 1. Since you are on Windows, you might be able to do an ODBC-type master to a spreadsheet. However, it appears that you might have to regen the master every time you add a new branch. One other thought comes to mind that is a very old technique and that is using MODIFY FIXFORM to decode your input file and load the data into a Focus data base that you could then report against. I don't know how big your file is but this would be fairly easy to do. Let me know if you want more details on this. 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 | |||
|
| Guru |
I would want to take a step back and ask if I could get the data in the format I need it. Someone has raw data that they are summarizing for you and choosing to present it in a particular format. Why couldn't they provide it as "SUM VALUE, BY Date, BY Branch"? You can work with the data in the format they are giving it to you, but is it a good use of resources to do it that way? ttfn, kp Access to most releases from R52x, on multiple platforms. | |||
|
| Master |
OCCURS was orginally created for VSAM files and hierachial databases (IMS, IDMS). You ask if it could work in this instance, probably. However, you say that changing the MFD is unacceptable. Your answer then becomes NO because you would have to change the OCCURS ## TIMES everytime a new brach is added. I would discourage you from trying this. Maintenance on the MFD for a comma delimited file is a nightmare even without an occurs clause. Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes | |||
|
| Silver Member |
Thank you for your responses. I guess you've confirmed what I knew, the data format has to change! Thanks again, P | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 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 |