Function to extract all combinations of Passed Amounts 3

[Mickbw]

Hi,

I have a query that could return any number of transaction amounts. For example there could be 5 transactions with amounts of amount1,amount2,amount3,amount4,amount5.

Is there a way to get an array of all the possible values from combination of these amounts. Code Samples would be greatly appreciated.

Sincerely,

Michael

Michael Brennan-White
New Hampshire Treasury Department

http://www.nh.gov/treasury

 

[MarciaAkins]

Hi Michael.

>> Is there a way to get an array of all the possible values from combination of these amounts <<

SELECT DISTINCT transaction_amount FROM MyAlias INTO ARRAY laAmounts



Marcia G. Akins

http://www.TightlineComputers.com

 

[rgbean]

Michael,
When you say "query", what exactly to YOU mean? Is it a SQL SELECT? (On a VFP database? On a remote Database - e.g. SQL Server or Oracle?) These could all return a Cusor - why use an array? Is it a call on a COM server or maybe a Web service? These would likely return and standard or XML object .

Rick

 

[Mickbw]

Sorry,

I wasn't specific enough in what I was asking for.

For example:
Amount1 = 1
Amount2 = 2
Amount3 = 3
Amount4 = 4
Amount5 = 5

I would like to pass these values and receive all combinations of these variables. For example:
Amount1 + Amount2
Amount1 + Amount3
Amount1 + Amount4
Amount1 + Amount5
Amount1 + Amount2 + Amount3
Amount1 + Amount3 + Amount4

Can I do this short of coding for each possible combination?

Thanks for the quick replies,

Michael

Michael Brennan-White
New Hampshire Treasury Department

http://www.nh.gov/treasury

 

[rgbean]

Michael,
Ah! Words can be so troubling at times.

This is really just a typical recursive procedure call problem. I've written them before, but the critical part is doing it so you don't exceed the call stack limitations of VFP.

Do you have a practical "limit" on the number of transactions you might have to deal with? The number of combinations builds up rather quickly, and depending on your requirements you may need to use different routines. (The "bulletproof" unlimited ones tend to take a lot longer to precess!)

Rick

 

[Mickbw]

Rick,

The number of transactions would not exceed 10. When one of the amounts matched I would probably restart the process with the remaining values.

Michael

Michael Brennan-White
New Hampshire Treasury Department

http://www.nh.gov/treasury

 

[craigsboyd]

Mickbw,

Well I don't know if you are still working on this or not. But I had a little time and thought it was a kind of cool problem given that it dealt with combinations. Here's a running example of how to deal with this...there is no recursion so you are limited only by the fact that you insisted that it drop the values into an array (65,000 elements max)...luckily there are only 1013 combinations given 10 amounts so you are well within the limits. The code is pretty fast, I've slowed it down some by adding all the stuff to show you the information on the screen and allow you to see it in the debug window afterwards...once you remove that fluff I think you'll agree that speed is not an issue with this. You'd have to add an ascan() or something to it if you wanted to disregard duplicate amount sums. Anyways, if you have any questions please feel free to ask.

CREATE CURSOR crsAmounts (Amount I) &&Fake Data
FOR i = 1 TO 10 && Fill up cursor with some values
	INSERT INTO crsAmounts (Amount) VALUES (i)
ENDFOR

CLEAR

PUBLIC lnTotal && Public so we can see the value after the run
PUBLIC ARRAY aryAmounts(1,2) && Public so we can look at the array after code has run
aryAmounts = ""
lnTotal = 0
SELECT amount FROM crsAmounts INTO ARRAY aryGroup
FOR i = 2 TO 10 &&assuming that at least 2 items have to be chosen from the set, if not then make it a 1 or whatever
	GetCombinations(i,@aryGroup)
ENDFOR
*!* Now go look at the array aryAmounts in the locals debug window

*************************
PROCEDURE GetCombinations(tcSize, tcSet)
*************************
	LOCAL lnMembers, lnCounter, lnTemp
	lnMembers = ALEN(tcSet)
	DIMENSION aryIndice(lnMembers)
	aryIndice = 0
	DO WHILE .T.
		IF aryIndice(1) < 1
			lnCounter = 1
			FOR lnCounter = 1 TO tcSize
				aryIndice(lnCounter) = lnCounter
			ENDFOR
				SaveAmount(lnMembers, @tcSet, @aryIndice)
			LOOP
		ELSE
			lnCounter = tcSize &&- 1
			DO WHILE lnCounter >= 1 AND aryIndice(lnCounter) >= (lnMembers - tcSize + lnCounter)
				lnCounter = lnCounter - 1
			ENDDO
			IF(lnCounter >= 1)
				aryIndice(lnCounter) = aryIndice(lnCounter) + 1
				lnTemp = lnCounter
				DO WHILE lnTemp < tcSize
					lnTemp = lnTemp + 1
					aryIndice(lnTemp) = aryIndice(lnTemp-1) + 1
				ENDDO
				SaveAmount(lnMembers, @tcSet, @aryIndice)
				LOOP
			ELSE
				EXIT
			ENDIF
		ENDIF
	ENDDO
ENDPROC

*************************
Procedure SaveAmount(tcMembers, tcSet, tcIndice)
*************************
	LOCAL lnCounter, lcString, lnAmountSum
	lnTotal = lnTotal + 1
	lcString = ""
	lnAmountSum = 0
	FOR lnCounter = 1 TO tcMembers
		IF tcIndice(lnCounter) != 0
			lnAmountSum = lnAmountSum + tcSet(tcIndice(lnCounter))
			lcString = lcString + TRANSFORM(tcSet(tcIndice(lnCounter))) + " + "
		ENDIF
	ENDFOR
	IF !EMPTY(aryAmounts(ALEN(aryAmounts)))
		DIMENSION aryAmounts(ALEN(aryAmounts,1) + 1,2)
	ENDIF
	aryAmounts(ALEN(aryAmounts,1),1) = lnAmountSum
	aryAmounts(ALEN(aryAmounts,1),2) = Left(lcString,LEN(lcString) - 3)
	?Left(lcString,LEN(lcString) - 3) + " = " + TRANSFORM(lnAmountSum) && So you can see it processing
ENDPROC

[sub]craig1442@mchsi.com[/sub][sup]

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

[/sup]

 

[Mickbw]

Craig,

Your post deserves more than a star. That is exactly what I needed.

Thanks,

Michael

Michael Brennan-White
New Hampshire Treasury Department

http://www.nh.gov/treasury

 

[craigsboyd]

Mickbw,

Glad it works for you. Thanks for the star, I enjoyed working on it.

[sub]craig1442@mchsi.com[/sub][sup]

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

[/sup]