How to get only a date range from a TPS file?

[QBTeKCom]

Hi, I am new to Clarion (we have 5.5) and we have a small program that reads a large TPS file and extracts some data to flat file. The file is from another program which we do not control.

Since the file is so big it takes 3-5 minutes. Most of the data is old and needs not be read. I need a way to only let clarion read a data range.

If we put in the date check in the code it sill takes 5 mins since Clarion is giving us each record.

Is there a way to just have clarion read a date range?

Thanks
Jack

 

[ShankarJ]

Jack,

Post your code for a mode definite answer.

I assume that you have a key based on the date with the Date column being the first element of the key. In that case, the code below should work :

CLEAR(FIL:Record)
FILate = FromDate
SET(FILateKey,FILateKey)
LOOP
NEXT(File)

IF ERRORCODE() OR FILate > ToDate THEN BREAK.

.... code to export to flat file
END

I think maybe you have missed on the SET(Key,Key) usage.

HTH-Regards

 

[QBTeKCom]

It is not that easy to post the code. I am not every sure which part of the code I should be looking at. (it is not my code and I have never worked with Clarion before).

No the date is not a key. The file comes from another program outside of our control. I don't even know enough on how to set the date as key and what it impact might be.


But even this code from above
IF ERRORCODE() OR FILate > ToDate THEN BREAK.

would be reading the entire table until the date breaks, correct.

It would work if the Date key were in DESECDING order so the questions

1.
Does the SETTING of the date key take a lot of time? (Does it have to go throught the entire file)? I am not sure if this would have to be done once per execution or once a month since I do not know how often then table gets overwritten or refreshed, etc.).

2.
How do you Set the date key? Is it the SET that you have above
SET(FILateKey,FILateKey)
or something else????

Jack

 

[QBTeKCom]

Additional info. The TPS files gets updated daily. It is not clear yet if this is a completely new file or just updated.

Jack

 

[ShankarJ]

Jack,

There are too many unknowns to help you constructively.

Don't you have the data structure of the Table/File to post?

When you do a SET(Key) it uses the key order to process the whole file from top to bottom. Whereas, when you do a SET(Key, Key) after populating the data elements of the key (first to last), it starts processing from the first data that matches (greater that or equal) the data value of the
key elements.

So if you have a file which has date records from Jan 1, 2006 to Dec 31, 2006 :
- Using SET(Key) will start from Jan 1, 2006
- Setting Date Field to Jan 15, 2006 and using SET(Key, Key) will start from Jan 15, 2006 onwards.

Since you are new to Clarion, I am not sure if I can help you further WITHOUT posting the file structure. To get the File structure, if you using the Clarion 5.5 IDE, open the application, choose the Module Tab, on the first module (<ApplicationName>.clw) right click and choose Module. It will show you the generated code from which you can cut and paste the File Structure here.