Using Dictionary, DumpDictionary, DeleteDictionary, and arraycolumns for tricks!

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

    Window wMixes
    deletedictionaryvalue MixLbsDict,""
            //programatically picks a year and gets the Mix LBS sold for it
            //puts those into a dictionary of Code=Lbs
            field (YrHolder)
            setDictionaryValue MixLbsDict, str(«Mix Parent Code»), LbsHold
        until info("stopped")
    ///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
  ;  debug
        if ItemNumCounter<arraysize(DictNamesArray,¶)+1
            window wPurch
            field (MixYrHold)
            select MixPercDict contains ItemSelect
                if info("empty")
                    repeatloopif YrCounter<info("records")+1


            ///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 
                //this finds which element of the array of x=y pairs (Dumped Dictionary) 
                //in MixPercDict
                //has the value we're looking for
                //this uses that element choice to grab the percent
                field (MixYrHold)                        
                //This does the appropriate math
                //then adds to a dictionary that we'll use later
                deletedictionaryvalue MixWeightItemDict,""    
                setdictionaryvalue MixWeightItemDict, ItemSelect,str(MixLbsItem)
                dumpdictionary MixWeightItemDict, AppendToMixWeight
                //This Makes sure that any duplicate entries are deleted
                //instead of making a mess of doubled totals
                arraydeduplicate AppendToMixWeight,AppendToMixWeight,¶
                arraystrip AppendToMixWeight, ¶
                «» = AppendToMixWeight
            until info("stopped")  
    until ItemNumCounter>arraysize(DictNamesArray,¶)

if YrCounter≠4
goto ChangeYr