Query multiple values in a given field 1

[ssilver]

I'm faced with a table which has several values in a given field all separated by commas, e.g. 123,456,7,*,111,293,...
There is always a comma at the end of the respective field in each record.

I'll probably be putting the substrings in a dynarray element from the ANSWER table. I'll determine that after the query is successfully performed.

Can anyone suggest how a single query can be constructed to perform the extraction of multiple values all from that given field?

Thanks,

ssilver

 

[JoeNg]

To find if a field contains, say, 456 and 999
Your query would look something like this.

..456.. or ..999..

Of course, there is a problem with this. If you have values like, 456, 23456, 45678 etc.
This query will match all these values.

Now, it does not make sense to have multiple values in one field.
If you want a specific value, you should break the values into separate field.

 

[JoeNg]

Based on the information you have given. I cannot see any way where you can query a particular value in the string of values, using only query.

I think you have 2 choices.
1. Continue with the current data structure. Use queries and codes to further narrow down the values you want as much as possible. However, there is no guarantee you will extract the correct value.
2. Restructure the data structure and get it over with. Now, you have something you can work with.

I believe, eventually, you will have to restructure the table to make it work.

 

[JoeNg]

A little more info on choice #1
Instead of using query, you can scan through the table, use breakapart command to separate the field values into an array using comma as the delimiter. Now you can search for the value(s) you want from that array. It might work, but it could be time consuming and you will have to write some codes. Of course, if the search criteria are changed, you will have to modify the codes. Now, that could be a major pain.

 

[rbooker]

I created a table Testfield.db with one field called Testfield and the following data:

123,456
123,456,874
123,456,874,8633
123,456,874,863,989

Using the following in an interactive query:

..","863",".. or ..","863

I was able to select only record #4 containing the value "863":

123,456,874,863,989

The double-quotes around the commas in the query string cause Paradox to treat the commas as part of the data and not as special characters.

Hope this helps.

 

[ros]

>..","863",".. or ..","863

this takes care of 863 in middle or end of field but you would also need
or 863","..
in case your value was at the beginning of multiple values and
or 863
in case your value was by itself in the field and if there is no comma

HTH,
Richie

 

[ssilver]

Thanks, Guys, for your responses. I agree that the table I'm faced with should not have been structured with multiple values in one field with the possibility of having what you suggested may happen. However, this system was turned over to me for continuous maintenance so I have to pick up on where it was left.

The only approach I can think of is to do a query on each member of a finite set of valid values that are used. The big problem there is that when there is an addition or change, the code must be changed and not allowing for a dynamic approach.

To me that will be a pain!!! Can you think of a different way to handle this?

Richie, I'm playing around with the breakapart function. I just didn't realize that the receiving array wasn't limited to one element as appears to be happening. I can't index it conveniently. Do you have any secrets?

Thanks again,

Stan

 

[Guest_imported]

As far as I understand you want to use a query to separate the contents of a single field that contains data put together that should be separated into more fields and not specifically find a particular value in the table.

You could import/export the table into/to another program that could handle what you need to do, then bring the new table into the original program (Paradox).

You could get Monarch for Windows from Datawatch Corp.

http://www.datawatch.com

I used to use version 2.0, now they are at version 6. You may be able to purchase an older version from somebody else if you want to save money. It is a wonderful program for importing data from older systems. This program for example can read text files and thru a filter (that you establish in the program) separate portions of text the way you want it. Then it converts the selection into a table. In your case you could

These are the instructions for Monarch version 2.0 (that I own):

1) export the table to a text file (probably Monarch can also read the table directly).

2) perform the filter by using a model file created by you in Monarch to obtain the new table. Monarch's Model files can be saved and are reusable. The first time will take a little work, then the following times are a matter of applying the saved model (the filter) again.

3) export the new table from Monarch into a table readable by your program.

*********
Another option (if you could not purchase Monarch):

Assuming that the commas are evenly placed on all the data: duplicating the table, then manipulating the table copy by duplicating the original column several times with a table restructure and data inserting from the original column to the others, then reducing the field width of the copied columns or manipulating the additional columns.

i.e. original table:
Original table:
Column 1
123,456,789

New table:
Column 1 Column 2 Column 3 Column 4
123,456,7890 123,456,7890 123,456,7890 123,456,7890

New table after changes:
Colum 1 Column 2 Column 3 Column 4
123,456,7890 123 123,456 123,456,7890

With further queries the left side of columns 3 and 4 are removed:

Column1 Column 2 Column 3 Column 4
123,456,7890 123 456 7890

César P.

http://www.keen.com/cesarinternational

 

[ros]

>> I'm playing around with the breakapart function. I just didn't
>> realize that the receiving array wasn't limited to one element as appears
>> to be happening. I can't index it conveniently. Do you have any secrets?

The array size will be defined by the number of elements in the field.

See " contains" under array in help system
if array.contains("value&quot then....

scan tc:
fieldval = tc.fieldname
fieldval.breakapart(arrayname, ",&quot
if arrayname.contains("valuesought&quot then
; handle it here
endif
endscan

Richie

 

[ssilver]

I got the breakApart function to work, but I can't seem to split the index of each element apart from its value. Both of these values appear in the array used as the argument in the BreakApart function. I may need a two dimentional array, at least.

Help!!

Thanks,
Stan

Here is the main code. It's kind of messy due to my trying a bunch of options. --
Several of the "codes" fields being analyzed:
111,*,*,7,*,67,*,67,
111,867,867,867,
89,867,
I only want the three digit fields.
The declarations and queries were done before this. And I've taken some of the commented statements out.
scan tcAnsw:
recCnt = tcAnsw.recNo()
s = tcAnsw."codes"
s.view()
;ar.view()
s.breakapart(ar, ",&quot
ar.view("after breakApart&quot
if ar.contains("*&quot OR ar.contains("111&quot OR ar.contains("7&quot then ;this is here so I don't process
;every record. It's just a test.
recCnt.view("recCnt&quot
ar.view("ar&quot
i.view("i&quot
for k from 1 to i*2
if ar[i+k].isAssigned() then
k.view("k&quot
ar[i+k].view("ar[i+k]&quot
endif
endFor
{
index1 = ar.substr(1,1)
index2 = ar.substr(2,6)
index1.view("index1&quot
index2.view("value&quot
}
if ar.isBlank() then quitLoop endIf
{
for j from 1 to 8
if ar.isBlank() then quitLoop endIf
dynSIG["element"] = string(j)
dynSIG["element"].view("dynSIG element&quot
dynSIG["codes"] = ar
dynSIG["codes"].view("dynSIG&quot
endFor
}
ar.view("line xxx&quot

ar2[j] = string(tcAnsw.recNo())
;if string(ar) = "111" OR string(ar) = "*" then i = i + 1
oneRec = string(ar2[tcAnsw.recNo()]) + " " + string(ar)
oneRec.view("oneRec&quot
j = j + 1
endscan

 

[ros]

What is this:
> i.view("i&quot
> for k from 1 to i*2
> if ar[i+k].isAssigned() then
> k.view("k&quot
> ar[i+k].view("ar[i+k]&quot
> endif
> endFor

looks like you are displaying every element in the array from i + 1 up to i * 2, but what is i ?

Not really sure what you are try to get.
If you are looking for the position of your value in the array then

for i from 1 to ar.size() ; scan the array
if ar = value then ; check the value of element i
msginfo("Found","Value found at position " + string(i) )
quitloop ; quit FOR loop
endif
endfor


Richie

 

[ssilver]

Richie,

Sorry I didn't mean to leave that code there. I thought that there was some relationship between each index value as it appeared in the array and the value of the query that was an element in that resulting array. This was an incorrect approach of attempting to retrieve the respective values. How do I split apart the index and the value in the array that results from the codes field in each record?

Stan

 

[ros]

>How do I split apart the index and the value in the array that
> results from the codes field in each record?

?????
You're losing me. I don't know what your question means.
The array index and value are already "split"
.view() shows both but they are not combined.
If array.view() shows
1 322
2 434
3 555
this means that array[1] = 322 not "1 322", array[2] = 434 etc.

If this doesn't help, could you rephrase your question with an example?

Richie

 

[ssilver]

I want to store each value in a given codes field per record in a different cell of the breakapart array and be able to address these values indivually per record. So if I have 3 values in the first record, i.e. 111,238,775 and 542,854,235,674 in the second record, I need to index these values in a string dynArray dynSIG[] something like the following structure:

dynSIG[recNum,item#,itemValue] per record.

I then want to calculate the number of occurrences of a given value for all of the records that appear for a given date or date range. The RIV:ANSWER table would appear as follows:

rec# Date Codes
1 10/1/01 111,238,775
2 10/1/01 542,854,235,674
3 10/2/01 111,238,775,357,268
4 10/2/01 542,854,735,374,843,687
...
...
That's what the basic goal is.

Does that clarify what I'm after??

Stan

 

[ros]

I'd create a separate table
var
t table
tc, tc1 tcursor
arrayname array[] string
fieldval string
t = create "riv:__results.db" with
"date" : "D",
"recnum" : "I",
"CodeItem" : "S",
"Value" : "I"
key "date","recno","CodeItem"
endcreate

tc.open("riv:answer.db&quot
tc1.open("riv:__results.db&quot
tc1.edit()
scan tc:
fieldval = tc.fieldname
fieldval.breakapart(arrayname, ",&quot
if arrayname.size() > 0 then
for si from 1 to arrayname.size()
tc1.insertRecord()
tc1.date = tc.date
tc1.recnum = tc.recno()
tc1.CodeItem = si
tc1.value = longint(arrayname[si])
tc1.unlockrecord()
endfor
endif
endscan
tc1.endedit()
tc1.close()
tc.close()
; now riv:__results.db has all your results and you can query etc.

Hope this is getting closer to what you are after.

HTH,
Richie
ps. using __ (2 underscores) before the name means paradox will delete table upon exit. remove them if you want to hang onto the table.

 

[ssilver]

Richie,

Thank you very much. I think I can go from here to create the report I'm looking for. It'll take a couple more queries, but you helped me with the basics of what I was looking for.

Thanks again,

Stan
*:->*