Littletommy


Hello All...many thanks to all who helped with my previous post. I have hit one more 'snag' in preparing my data. Here's a sample of three fields in my table...

MRC REQUIRED DETAILS

4720-00-001-0057

ADLF HOSE OR TUBING SPEC/STD DATA MIL MIL-H-5593,SIZE 4
SPECIFICATION
ADJM INNER CONVEYING TUBE MATERIAL RUBBER, SYNTHETIC
MEDA MEDIA FOR WHICH DESIGNED AIR
CRWL INSIDE SURFACE CONDITION SMOOTH
CQCS LAYER COMPOSITION AND LOCATION 1ST LAYER ANY ACCEPTABLE
CQCS OUTER LAYER MOLDED RUBBER
CRJM OUTER COVERING ENVIRONMENTAL ABRASION RESISTANT AND
PROTECTION
CRJM FUEL RESISTANT AND
CRJM OIL RESISTANT
ADJS EXTERIOR COLOR ANY ACCEPTABLE
AAGR CROSS-SECTIONAL SHAPE STYLE 1 ROUND
AARX INSIDE DIAMETER 0.250 INCHES NOMINAL
ABKV OUTSIDE DIAMETER 0.500 INCHES NOMINAL
CRXX MEASURING METHOD AND LENGTH 59.000 INCHES NOMINAL WORKING
ACKH MINIMUM INSIDE BENDING RADIUS 4.000 INCHES
ADLG FIRST END FITTING SPEC/STD DATA MIL MS27404-4 STANDARD
ACKM FIRST END CONNECTION TYPE THREADED INTERNAL TUBE
ACKN FIRST END STYLE DESIGNATOR C3 SWIVEL NUT FLARE
ACTE FIRST END SEAT ANGLE IN DEG 37.0
ADKD FIRST END CONNECTION DESIGN STRAIGHT
CQBG FIRST END SWIVEL ACTION INCLUDED
CAPABILITY
CQGH FIRST END FITTING/COMPONENT AND COMPLETE FITTING ALUMINUM ALLOY,
MATERIAL QQ-A-225/6, ALLOY 2024

6310-00-125-5628

There will be a fourth field added (nsn) & the stock number (4720-00-001-0057 in this example) needs to populate each record until the next stock number in field 'required' is reached (the number will be different, but will always be in the format shown). Also, all records that contain additional 'required' or 'details' information need to be merged with the 'parent' record. For example, the record 'ADLF' would end up as:

nsn mrc required details

4720-00-001-0057 ADLF HOSE OR TUBING SPEC/STD DATA MIL-H-5593,SIZE 4 SPECIFICATION

and the record 'CRJM' would end up as:

nsn mrc required details

4720... CRJM OUTER COVERING ENVIRONMENTAL PROTECTION ABRASION RESISTANT AND FUEL RESISTANT...

When we hit the next stock number (6310-00-125-5628), the process repeats (inserting the new stock number into the 'nsn' field).

Any assistance would be greatly appreciated!




Re: Help with expression (one more time...)?

CetinBasoz


Sounds like you have a very bad data design. Anyway:

local lcStockNo
lcStockNo = ''
scan
if empty( chrtran( required, '0123456789-', '') )
lcStockNo = required
else
replace nsn with m.lcStockNo
endif
endscan






Re: Help with expression (one more time...)?

Littletommy

Many Thanks...but there seems to be a problem with this. It works up to the point where it encounters a blank 'required' field & then stops populating the nsn field until it hits the next stock number in the 'required' field. Like this..........

mrc required details nsn

4720-00-885-7219
ADJM INNER CONVEYING TUBE MATERIAL RUBBER, SYNTHETIC 4720-00-885-7219
CRWL INSIDE SURFACE CONDITION SMOOTH 4720-00-885-7219
CQCS LAYER COMPOSITION AND LOCATION 1ST LAYER MOLDED RUBBER, 4720-00-885-7219
SYNTHETIC
CQCS 2ND LAYER BRAIDED WIRE
CQCS OUTER LAYER BRAIDED TEXTILE YARN
ADJS EXTERIOR COLOR ANY ACCEPTABLE
AAGR CROSSSECTIONAL SHAPE STYLE 1 ROUND

4720-00-885-7573
MATL MATERIAL RUBBER, SYNTHETIC 4720-00-885-7573
AFFA COVER MATERIAL RUBBER, SYNTHETIC 4720-00-885-7573
STYL STYLE DESIGNATOR C1 CONTINUOUS CURVE 4720-00-885-7573
ABNZ EFFECTIVE LENGTH 8.625 INCHES NOMINAL 4720-00-885-7573
AGSJ BEND RADIUS 9.000 INCHES NOMINAL 4720-00-885-7573
BZRX ENLARGED END NOT PROVIDED 4720-00-885-7573
AAGT WALL THICKNESS 0.200 INCHES NOMINAL 4720-00-885-7573
AYHW HOSE INSIDE DIAMETER 1.750 INCHES NOMINAL 4720-00-885-7573
AGSK PROTECTIVE CAP NOT INCLUDED 4720-00-885-7573
ZZZK SPECIFICATION/STANDARD DATA 24161-4276 MANUFACTURERS STANDARD 4720-00-885-7573

The routine also does not address the 'second' part of my problem which involves merging the data from the 'required' and 'details' fields in subsequent 'like' mrc records into the first mrc record. Here's what I start with...

mrc required details

recno1 CRJM OUTER COVERING ENVIRONMENTAL ABRASION RESISTANT AND
PROTECTION
recno2 CRJM FUEL RESISTANT AND
recno3 CRJM OIL RESISTANT

Needs to become.........

mrc required details

recno1 CRJM OUTER COVERING ENVIRONMENTAL PROTECTION ABRASION RESISTANT AND FUEL RESISTANT

recno2 CRJM
recno3 CRJM

The 'details' line above gets truncated when I post this. It should read....

ABRASION RESISTANT AND FUEL RESISTANT AND OIL RESISTANT

Once this is accomplished, I can delete the records where the 'required' and 'details' fields are blank.







Re: Help with expression (one more time...)?

CetinBasoz

local lcStockNo
lcStockNo = ''
scan
if !empty( required ) and empty( chrtran( required, '0123456789-', '') )
lcStockNo = required
else
replace nsn with m.lcStockNo
endif
endscan




Re: Help with expression (one more time...)?

Littletommy

Thanks...that took care of populating the nsn field...works fine. Any ideas on how to accomplish 'part 2' of my dilemma in regards to merging the data from subsequent records into the 'parent' record as shown above





Re: Help with expression (one more time...)?

CetinBasoz

You may ask that as another question. I feel I'm helping you go on with your bad data design:( To me it looks like the data is coming from a fixed length text file or from the start you've used VFP tables as a kind of notepad where records were rows of a lined paper.

You'd do a loop similar to the above. ie:

create cursor newtable ( rcno i,required c(100), details m )
scan
if !empty(required)
scatter memvar memo
rcno = recno()
skip
scan while empty(myTable.required)
m.details = m.details + ' ' + myTable.details
endscan
insert into newtable from memvar
skip -1
endif
endscan





Re: Help with expression (one more time...)?

Littletommy

You are correct in regards to the data design. Unfortunately this is a dataset is maintained by a third party & the export is not very 'user friendly'. In essence, to extract the data I have to export to a text file & then import into VFP. When this is done, I end up with the structure I've been indicating. I wish there were a better way, but I'm stuck with it....all I can do is try to massage what I have.

Thanks again for all the help.....but when I run the routine above to merge the fields, the cursor is empty (returns 0 records). Any additional thoughts would be appreciated!





Re: Help with expression (one more time...)?

CetinBasoz

Paste code to create that cursor with data in it and I would then test.

You can create sample data code wit this routine:

_cliptext = SampleData2Text('customer',20) && Put customer' s first 20 rows into clipboard

*SampleData2Text.prg
Lparameters tcTableName,tnRecords
Use (m.tcTableName) In 0 Alias 'sampling'
Select 'sampling'
Local lcTemp, ix, lcData, lcRetVal
lcTemp = Sys(2015)+'.tmp'
If !Empty(m.tnRecords) And Type('m.tnRecords') = 'N'
 Copy To (m.lcTemp) Type Delimited Next m.tnRecords
Else
 Copy To (m.lcTemp) Type Delimited
Endif
lcData = Filetostr(m.lcTemp)
Erase (m.lcTemp)
 
Set Textmerge Delimiters To '%%','%%'
Set Textmerge To (m.lcTemp) Noshow
Set Textmerge On
\CreateCursor_%%m.tcTableName%%()
\
\Procedure CreateCursor_%%m.tcTableName%%
\LOCAL lcData, lcTemp
\lcTemp = SYS(2015)+'.tmp'
\TEXT TO m.lcData noshow
\%%m.lcData%%
\ENDTEXT
\
\STRTOFILE(m.lcData,m.lcTemp)
\
\create CURSOR %%m.tcTableName%% ;
\ ( ;
For ix=1 To Afields(aStruc,'sampling')
\ %%IIF(m.ix > 1,',','')%%
\\%%aStruc[m.ix,1]%% %%aStruc[m.ix,2]%%(%%aStruc[m.ix,3]%%,%%aStruc[m.ix,4]%%)
\\ %%IIF(aStruc[m.ix,5],'',' NOT ')%% NULL
\\ %%IIF(aStruc[m.ix,6],'NOCPTRANS','')%% ;
Endfor
\ )
\
\APPEND FROM (m.lcTemp) TYPE delimited
\ERASE (m.lcTemp)
\endproc
Set Textmerge To
Set Textmerge Off
Set Textmerge Delimiters
lcRetVal = Filetostr(m.lcTemp)
Erase (m.lcTemp)
Use In 'sampling'
Return m.lcRetVal




Re: Help with expression (one more time...)?

Littletommy

Whew...you lost me on that one. Just a novice here...sorry.





Re: Help with expression (one more time...)?

AndyKr

>> Whew...you lost me on that one. Just a novice here...sorry.

I think you need to explain exactly what it is you are trying to do. Cetin has given you a lot of code (and time!) and if you can't interpret how it solves your issue, then maybe the issue is wrong.

Can you show what your source data looks like, and how you want the end result to appear






Re: Help with expression (one more time...)?

Littletommy

Hello Andy,

Yes, Cetin has been a great help & it's much appreciated. Unfortunately, I have only rudimentary VFP skills and he 'lost' me on the last post.

This is the sample that I had posted earlier...

mrc required details

CRJM OUTER COVERING ENVIRONMENTAL ABRASION RESISTANT AND
PROTECTION
CRJM FUEL RESISTANT AND
CRJM OIL RESISTANT

Needs to become.........

mrc required details

CRJM OUTER COVERING ENVIRONMENTAL PROTECTION ABRASION RESISTANT AND FUEL RESISTANT AND OIL..

CRJM
CRJM

The 'details' line above gets truncated when I post this. It should read....

ABRASION RESISTANT AND FUEL RESISTANT AND OIL RESISTANT

In essence, records of like ¡®mrc¡¯ with empty ¡®required¡¯ fields have additional lines of ¡®decoded¡¯ text in subsequent like 'mrc' records. The intent is to combine all of these subsequent like mrc 'required' fields into the first record and all of the like mrc 'details' fields into the first record.

Thanks!





Re: Help with expression (one more time...)?

AndyKr

OK, so let me make sure I understand what you want.

You currently have multiple records for each value of "MRC" and you want to create a single record for each unique value of the "mrc" which concatenates all of the data from the individual records. In other words you want to turn this:

MRC Required Detail
CRJM Some data Additional Data
CRJM More Stuff
CRJM Yet more stuff
XXXY New Value

into this:

MRC Required Detail
CRJM Some data More Stuff Additional Data Yet More Stuff
XXXY New Value

Is that correct






Re: Help with expression (one more time...)?

Littletommy

Yes Andy...that is correct.



Re: Help with expression (one more time...)?

AndyKr

Paste this code into a Program and run it.

Then study the code and you should be able to adapt it ot do whatever you need.

*** Here is some test data in the "old style"
CREATE CURSOR old_style ( mrc C(10), required M, detail M)
INSERT INTO old_style VALUES ('CQCS', 'LAYER COMPOSITION AND LOCATION', '1ST LAYER MOLDED RUBBER, SYNTHETIC'
)
INSERT INTO old_style VALUES ('CQCS', '2ND LAYER BRAIDED WIRE', 'OUTER LAYER BRAIDED TEXTILE YARN' )INSERT INTO old_style VALUES ('ADJS', 'EXTERIOR COLOR', 'ANY ACCEPTABLE'
)
INSERT INTO old_style VALUES ('CQCS', '', 'C1 CONTINUOUS CURVE '
)
INSERT INTO old_style VALUES ('AAGR', 'CROSSSECTIONAL SHAPE STYLE', '1 ROUND' )

*** Make sure the data is sorted on MRC
INDEX ON mrc TAG mrc

*** Now we need somewhere to collect the "new"
CREATE CURSOR new_style ( mrc C(10), required M, detail M)

*** Create a couple of variables
lcMRC = "" && This will be the Key Value tracker
lcReq = ""
&& Accumulate the "Required" column here
lcDet = ""
&& Accumulate the "Detail" column here

*** Start in the old Data
SELECT old_style
GO TOP
SCAN
*** Now, check for change of MRC Value
IF NOT (lcMRC == ALLTRIM( UPPER( old_style.mrc )))
IF NOT EMPTY( lcMRC )
*** We have some data already, so insert it into the new cursor
INSERT INTO new_style (mrc, required, detail ) VALUES ( lcMRC, lcReq, lcDet )
*** Initialize the accumulators
STORE "" TO lcReq, lcDet
ENDIF
*** Get the new key
lcMRC = ALLTRIM( UPPER( old_style.mrc ))
ENDIF

*** When we get to here, we just want to accumulate the data - adding Line Feeds (optional)
lcReq = lcReq + ALLTRIM( old_style.required ) + CHR(13) + CHR(10)
lcDet = lcDet +
ALLTRIM( old_style.detail ) + CHR(13) + CHR(10)
ENDSCAN

IF NOT EMPTY( lcMRC )
*** Handle the final record here - because the SCAN will "drop out" at the end
*** so the last set of data is still in the variables....
INSERT INTO new_style (mrc, required, detail ) VALUES ( lcMRC, lcReq, lcDet )
ENDIF

*** Take a look at the result here
SELECT new_style
BROWSE NORMAL NOCAPTION

Any questions, ask!






Re: Help with expression (one more time...)?

Littletommy

Andy,

Thanks much for the assistance...this seems to work fine. Only one question...when I move the memo field data into character strings, the linefeeds appear. If I process the file without the linefeeds, all the text is run together. Is there any way to replace the linefeeds with a space in the character string