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     [SOLVED] Modify hold file

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Modify hold file
 Login/Join
 
<Kev>
posted
I've an uncommon situation, and I'm trying to figure out the best possible way to do this:
I've a HOLD file which might have some missing columns. Based on the contents of existing rows in the file, I've logic to figure which rows to add (and, how to derive it's contents).
What I ideally want to do is iterate over the table rows, save some values in temporary variables, and then if a condition matches, insert a row.
I can't figure out any way to do this. Any help will be appreciated.

Thanks in advance.

This message has been edited. Last edited by: Kerry,
 
Report This Post
Expert
posted Hide Post
There are several ways to add records to a hold file.


  • Macgyver Technique
  • MODIFY (Focus/Relational)
  • FILEDEF (APPEND
  • FML/FRL/EMR/SML etc


It depends on the indiviuals case and preferences.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Guru
posted Hide Post
Hi Kev,

Do you want to add missing columns or missing rows? It was not really clear in you question.

In case of missing columns you can use JOIN's with TABLE FILE or MATCH. Your logic can then be put in define's or compute's.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
<Kev>
posted
Hi Frans,

I want to add missing rows. Columns stay intact.
 
Report This Post
Expert
posted Hide Post
I'd recommend along the lines of Waz' response to use either McGuyver or Universal Concatenation (MORE).

You can't do a MODIFY on a fixed-format HOLD file. You could load the rows into a temp FOCUS file using MODIFY, then load the new rows, then TABLE out into another hold file.


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
<Kev>
posted
Hi Waz,

I can't find a good example of your mentioned techniques.
I want to in pseudo language do this:

LOOP OVER TABLE T
FOR EACH REC IN T
IF REC.COLUMN1 NOT EXISTS IN OTHER ENTRIES OF COLUMN1
INSERT REC WITH REC.COLUMN2='CHANGED VAL'
ELSE
CONTINUE
LOOP OVER
END
 
Report This Post
<Kev>
posted
Hi Ginny,

I can use Universal Concatenation fine. My issue is iterating over the rows and storing those rows that I need to insert using MORE statement later on (after the loop) - if I can get example/guidance on it, I'll be all set.

Thanks much!
-----------------------------------------------------
 
Report This Post
Expert
posted Hide Post
How about first finding all the rows in T that only have 1 entry. I think based on your posts that is what you are looking for. You could then massage those and then use universal concatenation to put the two hold files back together.
TABLE FILE CAR
PRINT *
ON TABLE HOLD AS CARHOLD FORMAT ALPHA
END
TABLE FILE CARHOLD
COUNT ENTRIES 
BY BODYTYPE
PRINT *
BY BODYTYPE
WHERE TOTAL COUNT EQ 1
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS ONLYONE FORMAT ALPHA
END

The first table is just to get a flat hold file. You can do more massaging in the 2nd table request to get your columns in order.


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
<Kev>
posted
Hi Ginny,
That's a good start. Since both have the same tables, I can explain you exactly what I need to do.
So in the following, I need to make sure that there're exactly 3 entries of the combination {Country, Bodytype} (two randomly selected columns). So I need to first find which combination has a missing entry (less than 3 - it's a given that it'll be less than or equal, but never greater than 3). If any does, I need to take the content of the entire row with same {Country, Bodytype} combination, change one of the columns (say, `retail cost') and add it back in main table (via may be MORE).

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS CARHOLD FORMAT ALPHA
END
TABLE FILE CARHOLD
COUNT ENTRIES
BY BODYTYPE
PRINT *
BY BODYTYPE
BY COUNTRY
WHERE TOTAL COUNT EQ 1
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS ONLYONE FORMAT ALPHA
END


Thanks much!
------------------------------------------------
 
Report This Post
Expert
posted Hide Post
And you want to get rid of the original rows that had the missing stuff, correct?


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
<Kev>
posted
Hmm... in my case, entire row is missing, not the individual column values.

Thanks!
-----------------------------------------------
 
Report This Post
Expert
posted Hide Post
Then you have 2 files? I'm very confused.


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
<Kev>
posted
Nah, just one file.
I know that that file is supposed to have 3 entries per combination {Country, Bodytype}, and that there're four such combinations.
 
Report This Post
Expert
posted Hide Post
quote:
Hmm... in my case, entire row is missing, not the individual column values.


If the whole row is missing, how can you test for combinations.

I'm not trying to be obnoxious, really I'm not. Maybe you should post some data and annotate it so we can get a better idea.


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
<Kev>
posted
Okay, I can't put in HTML formatted output here, so I'm going to try to explain my case with an example. Going with the table we've been discussing:

- I know {Convertible, England} (of {Body Type, Country}) should have three entries with retail_cost equal to {10000, 9000, 8000}. If any one of the entries {10000, 9000, 8000} is missing, I need to find that through existing entry (it's a given that there'll be one entry per combination for sure), and add that entry.
- There're four such combinations: {Convertible, England}, {Convertible, Italy}, {Roadster, England}, {Roadster, Italy}.
- So, in total, it has to have 12 entries.
- Entries for `retail_cost' are known in advance, and have to be {10000, 9000, 8000}.

This is a difficult problem to explain, so pardon my crude explanation.

I appreciate all the help.

Thanks once more!

-------------------------------------------------------------------------------

This message has been edited. Last edited by: <Kev>,
 
Report This Post
Expert
posted Hide Post
Would I be correct in saying that you have two fields, values unknown, but for each combination of those two fields there must be three rows of output?

If this is true then I would suggest that oen way is using the McGyver technique.

This example uses the sample database CAR and there are certain combinations of Country and Bodytype that do not exist. Using this code I can perform a loose join to provide every combination of those two fields. To extend that to have every combination of three fields is just a small change away -

DEFINE FILE CAR
  CTRY_KEY/A1 WITH COUNTRY  = ' ';
  BTYP_KEY/A1 WITH BODYTYPE = ' ';
END

TABLE FILE CAR
   BY CTRY_KEY
   BY COUNTRY
ON TABLE HOLD AS CTRYFILE FORMAT FOCUS INDEX CTRY_KEY
END

TABLE FILE CAR
   BY BTYP_KEY
   BY BODYTYPE
ON TABLE HOLD AS BODYFILE
END

JOIN CLEAR *
JOIN BTYP_KEY IN BODYFILE TO MULTIPLE CTRY_KEY IN CTRYFILE AS J1

TABLE FILE BODYFILE
   BY COUNTRY
   BY BODYTYPE
END

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
Kev,

Your terminology is confusing me.

When you say 'entries', do you mean fields ?

I think you would get a lot farther with this problem if you used the terms 'record' and 'field'

quote:
So, in total, it has to have 12 entries.
- Entries for `retail_cost' are known in advance, and have to be {10000, 9000, 8000}.


I keep getting the feeling that you have all the records/rows that you need, but some of the fields are empty. And you could populate those field values with DEFINEs, as you intimated that you had the logic to do that.


Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Report This Post
<Kev>
posted
Hi Dave,

Sorry about the confusing terms.
Tony A. has hit the nail right on the head for my problem, and now I just need to tweak stuff a bit to get this working.
Thanks for all your help!
I'll keep a note of the terms for future help/contribution from/to this forum.

--------------------------------------------
 
Report 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     [SOLVED] Modify hold file

Copyright © 1996-2020 Information Builders