Hi Everyone! I’m one of those holdouts for Pan 6, and constantly trying to get it to do things that aren’t obvious.
This problem was trying to work with two data sets as they were, and get some usable data about how much of each item was used in a “Mix” of items. Items could be in any number of mixes, and had different percentages used for each item for each mix it was in, and this file was meant to help forecast how much seed to buy for the following year.
On top of that, we had the data for total lbs of each item, and total lbs of each “Mix”, but not the data for how that broke down for each year.
So, my solution was this:
For each item, from other sources we have:
-
gather up the total lbs we ended up using/have used currently for the last 4 years
-
gather the lineitemarray of items in each mix and turn that into a list of “which items are in which mixes”
(note, this is where the code snippet starts)
-
from that same set of line items, build a dictionary of Key/Value pairs where we hold onto
– Key: Mix Number / Value: the percentage of the mix that the selected ItemNum takes up -
Now, we have a small problem. There’s repeats of Mix numbers, so we can’t just keep adding to that key without replacing values or making a multi-dimensional value of Item/percent (which is doable, but overly complicated for the end user IMO)
-
so instead dump that dictionary to create multiple lines in each record, for each item, the mix/percentage and that means we can always reference that info in a form, or through breaking that array down more. Which we’re about to do.
-
build a dictionary of mixes and their lbs sold for a single year
-use the listdictionarynames to get an array of keys to loop through
Now for the fun part
- we use arraysearch on our dumped dictionary to find the correct percentage to do some math on. So we treat it as an array where
– Raw Data looks like this: “8004=0.4”¶“8062=0.1”¶
– and we need to find out which “element” has the correct percentage
– so, we say to panorama using arraycolumns, "Pretend this is an array delimited by “=” and ¶ and then tell me which element of the new “=” array “field” contains “8004”, for example
– great, so now do the same thing, but tell me what’s after the “=” to get the precent
– then dump those into a field to hold a list of Mixes, and the appropriate weights per year per percent per item
And then we built another function to loop through that dumped dicitonary and give us some totals for the whole list.
I’m overexplaining this, but damn if it wasn’t fun to build.
I hope someone in the future trying to use these functions can get any use out of them, and I would love any advice on how I could have streamlined this! I’ll link to the github of the whole code for this project if you wanna look it over!
//Builds Dictionary of MixNumber and The Total Pounds Sold for X Year
Window wMixes
selectall
YrCounter=0
;debug
firstrecord
ChangeYr:
Window wMixes
firstrecord
YrCounter=YrCounter+1
deletedictionaryvalue MixLbsDict,""
loop
//programatically picks a year and gets the Mix LBS sold for it
//puts those into a dictionary of Code=Lbs
YrHolder=arraybuild(¶,"","Yr1+¶+Yr2+¶+Yr3+¶+Yr4")
YrHolder=array(YrHolder,YrCounter,¶)
field (YrHolder)
clipboard()=«»
LbsHold=str(clipboard())
setDictionaryValue MixLbsDict, str(«Mix Parent Code»), LbsHold
downrecord
until info("stopped")
;counter2=YrCounter
;YrCounter=YrCounter+1
///Makes an array of that Dictionary's Parent Codes
//this lets us iterate through using the array fuction
listDictionarynames MixLbsDict,DictNamesArray
//find the percentage to do math with for each Mix Number
//loop through the whole DictNames array
ItemNumCounter=0
MixYrHold="MixWeightDictYr"+str(YrCounter)
; debug
RepeatDictLoop:
loop
ItemNumCounter=ItemNumCounter+1
if ItemNumCounter<arraysize(DictNamesArray,¶)+1
window wPurch
field (MixYrHold)
ItemSelect=array(DictNamesArray,ItemNumCounter,¶)
select MixPercDict contains ItemSelect
if info("empty")
ItemNumCounter=ItemNumCounter+1
repeatloopif YrCounter<info("records")+1
endif
LbsHold=val(getdictionaryvalue(MixLbsDict,ItemSelect))
///The next part is a complicated
/*
The arraycolumn is looking at the arrayed dictionary values that are ex. 1234=0.4 and saying to treat it
like if it was a tiny database where the fields looked like
column1 column2
1234 0.04
and I'm using that ability to separate them to find the correct value
and then to extract the correct percentage to do math with
*/
; debug
loop
//this finds which element of the array of x=y pairs (Dumped Dictionary)
//in MixPercDict
//has the value we're looking for
PercChoice=arraysearch(arraycolumn(ItemSelect,1,¶,"="),ItemSelect,1,¶)
//this uses that element choice to grab the percent
MixPercChoice=val(arraycolumn(array(MixPercDict,PercChoice,¶),2,¶,"="))
field (MixYrHold)
//This does the appropriate math
MixLbsItem=0
MixLbsItem=float(MixPercChoice)*float(LbsHold)
//then adds to a dictionary that we'll use later
deletedictionaryvalue MixWeightItemDict,""
setdictionaryvalue MixWeightItemDict, ItemSelect,str(MixLbsItem)
;debug
AppendToMixWeight=""
dumpdictionary MixWeightItemDict, AppendToMixWeight
AppendToMixWeight=«»+¶+AppendToMixWeight
//This Makes sure that any duplicate entries are deleted
//instead of making a mess of doubled totals
arraydeduplicate AppendToMixWeight,AppendToMixWeight,¶
arraystrip AppendToMixWeight, ¶
«» = AppendToMixWeight
AppendToMixWeight=""
downrecord
until info("stopped")
endif
until ItemNumCounter>arraysize(DictNamesArray,¶)
if YrCounter≠4
goto ChangeYr
endif