A Vertical Table from a Horizontal List? 4

[JasonCannon]

I need to create a table based on a list of names a numbers, like the example below.

Alpha;100;55, 77, 91, 93-99
Bravo;200;25, 29, 33, 44
Charlie;300;10-13, 15-18, 61, 67-70

The list is given in a semi-colon delimited text file.

I need to generate a table in the following format.

Name Code Number
Alpha 100 55
Alpha 100 77
Alpha 100 91
Alpha 100 93
Alpha 100 94
Alpha 100 95
Alpha 100 96
Alpha 100 97
Alpha 100 98
Alpha 100 99
Bravo 200 25
Bravo 200 29
Bravo 200 33
Bravo 200 44
Charlie 300 10
Charlie 300 11
Charlie 300 12
Charlie 300 13
Charlie 300 15
Charlie 300 16
Charlie 300 17
Charlie 300 18
Charlie 300 61
Charlie 300 67
Charlie 300 68
Charlie 300 69
Charlie 300 70

Getting the first two columns seem easy. I just seem to be running into problems with loops and getting the third column.

As you can tell, the given list specifies ranges of numbers, and I need to generate a record for each number within those given ranges. And there can be several ranges given per line, along with individual numbers.

Any help would be much appreciated.

 

[atlopes]

Something like this, Jason:

LOCAL Source AS String

TEXT TO m.Source NOSHOW
Alpha;100;55, 77, 91, 93-99
Bravo;200;25, 29, 33, 44
Charlie;300;10-13, 15-18, 61, 67-70
ENDTEXT

CREATE CURSOR TargetData (LeaderName Varchar(20), LeaderNum Int, SetValue Int)

LOCAL ARRAY Leaders(1), Sets(1), SetValues(1)

LOCAL LeaderIndex AS Integer, SetIndex AS Integer, SetValueIndex AS Integer, SetValueIntervalIndex AS Integer

FOR m.LeaderIndex = 1 TO ALINES(m.Leaders, m.Source)

	FOR m.SetIndex = 3 TO ALINES(m.Sets, m.Leaders(m.LeaderIndex), 1, ";")

		FOR m.SetValueIndex = 1 TO ALINES(m.SetValues, m.Sets(3), 1, ",")

			FOR m.SetValueIntervalIndex = VAL(m.SetValues(m.SetValueIndex)) TO ;
						MAX(VAL(m.SetValues(m.SetValueIndex)), VAL(GETWORDNUM(m.SetValues(m.SetValueIndex), 2, "-")))

				INSERT INTO TargetData VALUES (m.Sets(1), VAL(m.Sets(2)), m.SetValueIntervalIndex)

			ENDFOR

		ENDFOR

	ENDFOR

ENDFOR

BROWSE

 

[GriffMG]

Or perhaps:

CLEAR
m.CRLF = CHR(13)+CHR(10)
m.INPUTSTRING = "Alpha;100;55, 77, 91, 93-99"+m.CRLF
m.INPUTSTRING = m.INPUTSTRING + "Bravo;200;25, 29, 33, 44"+m.CRLF
m.INPUTSTRING = m.INPUTSTRING + "Charlie;300;10-13, 15-18, 61, 67-70"+m.CRLF

? PROCESSSTRING(m.INPUTSTRING)

FUNCTION PROCESSSTRING
PARAMETERS m.INPUTSTRING
PRIVATE m.INPUTSTRING,m.OUTPUTSTRING,I,X,Y,m.LINEDATA,m.POS,m.INDICATOR,m.SIZETHING,m.ITEMCOUNT
m.OUTPUTSTRING = ""
FOR I = 1 TO MEMLINES(m.INPUTSTRING)
	m.LINEDATA = MLINE(m.INPUTSTRING,I)
	IF !EMPTY(m.LINEDATA)
		m.POS =  AT(";",m.LINEDATA)
		IF m.POS > 1
			m.INDICATOR = LEFT(m.LINEDATA,m.POS-1)
			m.LINEDATA = SUBSTR(m.LINEDATA,m.POS+1)
			m.POS =  AT(";",m.LINEDATA)
			IF m.POS > 1
				m.SIZETHING = LEFT(m.LINEDATA,m.POS-1)
				m.LINEDATA = SUBSTR(m.LINEDATA,m.POS+1)
				m.ITEMCOUNT = ALINES(ALINEDATA,m.LINEDATA,",")
				FOR X = 1 TO m.ITEMCOUNT
					IF !EMPTY(ALINEDATA[x])
						IF !"-"$ALINEDATA[x]
							m.OUTPUTSTRING = m.OUTPUTSTRING + m.INDICATOR +" "+m.SIZETHING+" "+ALLTRIM(ALINEDATA[x])+m.CRLF
						ELSE
							m.POS =  AT("-",ALINEDATA[x])
							IF m.POS > 1
								m.FIRSTITEM = VAL(LEFT(ALINEDATA[x],m.POS-1))
								m.LASTITEM = VAL(SUBSTR(ALINEDATA[x],m.POS+1))
								FOR Y = m.FIRSTITEM TO m.LASTITEM
									m.OUTPUTSTRING = m.OUTPUTSTRING + m.INDICATOR +" "+m.SIZETHING+" "+ALLTRIM(STR(Y))+m.CRLF
								NEXT

							ENDIF

						ENDIF
					ENDIF
				NEXT
			ENDIF

		ENDIF
	ENDIF
NEXT


RETURN(m.OUTPUTSTRING)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

A tricky question, Jason. As you probably realise, there is no single command that will do what you want. The solution will require a fair amount of code. Nor would it be reasonable to expect the forum members to write the code for you (which I'm sure is not what you want).

That said, here is a possible approach. I'll just give you an outline of the method, and leave you to fill in the details for yourself.

1. Create a cursor (or table) with three fields: Name, Code, Ranges. We'll call this cursor Input.

2. Append your date into this table, using APPEND FROM ... TYPE DELIMITED ... (it looks like you have already got this far).

3. Create a second cursor (or table) to hold you final results. We'll call it Output. It will have three fields: Name, Code, Number.

4. Loop through the Input cursor. For each record, do the following:

4.1. Copy the contents of Ranges into an array, using ALINES().

4.2. Scan the array, using [tt]FOR x = 1 TO <length of array> / ENDFOR[/tt]

4.2.1. If the current array element contains a single number (e.g. 55), insert a record into Output. Copy the Name and Code fields from the current Input record, and place the contents of the current array element into Number.

4.2.2. If the array elements contains two numbers separated by a hyphen (e.g. 93 - 99), start an inner FOR / ENDFOR loop, using those two numbers as the boundaries (e.g. [tt]FOR x = 93 TO 99 / ENDFOR[/tt]).

4.2.2.1. Each time round that inner loop, insert a record into Output. Copy the Name and Code fields from the current Input record, and place the current value in the FOR loop (that is, the value of x in the above example) into Number.

I think that will do it (more or less). Note that the above is very much off the top of my head. I haven't made any attempt to actually write or test the code, or even to dry run the method on paper. Give it a try and come back if you run into difficulties.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Ha! In the time it took me to type my post, you received two good detailed replies - proving me wrong when I said that you can't expect forum members to write the code for you.

Looks like you've got some good stuff to work on.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

It was my bank holiday sudoku

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[JasonCannon]

atlopes & GriffMG,

Awesome posts! I can't thank you all enough. As Mike stated, I was expecting more of being pointed in the right directions, but the time you all have saved me will really help out. This is just one part of a larger project "bestowed" upon me here recently. Good stuff, indeed.

Much thanks!
Jason...

 

[JasonCannon]

I must admit, I am still studying the code while running the data I do have through it and someone through me a curve ball.

I am now seeing lines like this.

Delta;400;02-05, 2-5

Where the leading zeros have to be there. Otherwise I get dupes.

Delta 400 02
Delta 400 03
Delta 400 04
Delta 400 05
Delta 400 2
Delta 400 3
Delta 400 4
Delta 400 5

And I cannot just "remove" that data, . So, that is what I am trying to figure out now.

And it is looking like it is a small enough amount that I can handle afterward, by looking for dupes and correcting them.

Jason...

 

[Mike Lewis]

If 05 is different from 5, then your Number field needs to be a character type, not a numeric type. There is no other way of distinguishing those two values.

By the way - and on another point - Name and Number are not ideal names for fields, as those are reserved words (or, more precisely, key words). They probably do no harm in this case, but it's something to keep in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[atlopes]

Jason, continuing with the exercise of doing this with a series of nested FOR loops (changed statements are highlighted):

LOCAL Source AS String

TEXT TO m.Source NOSHOW
Alpha;100;55, 77, 91, 93-99
Bravo;200;25, 29, 33, 44
Charlie;300;10-13, 15-18, 61, 67-70
Delta;400;02-05, 2-5
ENDTEXT

CREATE CURSOR TargetData (LeaderName Varchar(20), LeaderNum Int, SetValue [highlight #FCE94F]Varchar(32)[/highlight])

LOCAL ARRAY Leaders(1), Sets(1), SetValues(1)

LOCAL LeaderIndex AS Integer, SetIndex AS Integer, SetValueIndex AS Integer, SetValueIntervalIndex AS Integer

FOR m.LeaderIndex = 1 TO ALINES(m.Leaders, m.Source)

	FOR m.SetIndex = 3 TO ALINES(m.Sets, m.Leaders(m.LeaderIndex), 1, ";")

		FOR m.SetValueIndex = 1 TO ALINES(m.SetValues, m.Sets(3), 1, ",")

			FOR m.SetValueIntervalIndex = VAL(m.SetValues(m.SetValueIndex)) TO MAX(VAL(m.SetValues(m.SetValueIndex)), VAL(GETWORDNUM(m.SetValues(m.SetValueIndex), 2, "-")))

				INSERT INTO TargetData ;
					VALUES (m.Sets(1), VAL(m.Sets(2)), ;
					[highlight #FCE94F]PADL(LTRIM(STR(m.SetValueIntervalIndex, 10, 0)), LEN(GETWORDNUM(m.SetValues(m.SetValueIndex), 1, "-")), "0")[/highlight])

			ENDFOR

		ENDFOR

	ENDFOR

ENDFOR

BROWSE

 

[atlopes]

And in the case you stumble upon an interval that adds another digit comparing with the previous step:

PADL(LTRIM(STR(m.SetValueIntervalIndex, 10, 0)), MAX(LEN(GETWORDNUM(m.SetValues(m.SetValueIndex), 1, "-")), LEN(LTRIM(STR(m.SetValueIntervalIndex, 10, 0)))

(as the third value in the INSERT statement).