This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Calgary is a list count data retrieval engine designed to perform analyses on large scale databases containing millions of records. One use case for a Calgary database in an App or Macro is to offer users certain predetermined criteria (e.g. geography) to select and then return the corresponding data to them. A back-end process can combine multiple data sources, format and pre-filter as needed, and load them into a Calgary database that can be queried quickly by the app or macro . This way, the bulk of the processing happens outside of the app or macro allowing data to be returned more quickly.
Update Allocate Append tool using XML
Works in 9.5 and 10.0
You’re running a process to select certain variables to be used within a model. You’ve built your process, but you’re getting tired of having to run it twice. Once to pull thousands of variables to check for relevance, and a second time with just the variables you want to include in the final model based on the tests you’ve run.
There’s good news! You can use the Action tool within the Interface toolset to update the Raw XML of the Allocate Append tool to dynamically select the variables you want to use, and it’s not as hard as you might think.
The first thing we need to do is find out what the XML code is for the variables we want to use, and the format it needs to be in for the Allocate Append tool to recognize it. You can enable the XML view from the User Settings menu (Options è User SettingsèEdit User Settings). On the Advanced tab, there is a check box to “Display XML in Properties Window”:
Once you’ve checked the box, return to your Allocate Append tool, or any tool on your canvas, and you’ll see a new option on the right hand side that will allow you to see the XML code the tool is creating.
From here you can get the format you need for the XML code that we’ll pass into the macro to be created later.
Once you know the variables you want to use, you can use the variable name (code, not description) to build out the XML string as show above. If you select multiple variables, what you’ll notice is that they are each on their own line under the “<Variables>” tag in the XML code. The list you make must follow the same format:
In the sample workflow attached, you’ll see that I am using a Text Input tool to simulate the data stream that contains the fully compiled XML strings needed. As you will most likely see in your data, I have one variable per record. The problem is I need all of the variables in the same cell, on their own line. So how do we combine the records into one, and add a new line?
The answer is we use the Summarize tool. Within the Summarize tool we can use the Concat function to combine the XML strings into a single cell, and in the concatenate Properties section, we can indicate that we want to use a new line as the separator by typing in \n.
Now that the prep work is done, all we need to do is pass this new variable list into the Allocate Append tool through XML. This can be achieved with a simple Batch Macro. For the Control Parameter you want to use the Variable list that we just created. The Control Parameter gets connected to the Allocate Append tool which adds the Action tool as shown below.
In your Action tool, select the option to “Update Raw XML with Formula”, expand the options under Allocate Append until you see “Variables” and highlight that section. You’ll want to update the Inner XML, and the formula to use is the connection from the Control Parameter as shown below.
Once you have this set up, simply add your Macro Input (for your incoming data stream) and Macro Output (to feed back into your workflow) to complete the macro set up.
Return to your original workflow, insert your newly created Batch Macro and connect your inputs. Your variable list stream will feed into the ¿ input, and your main data stream to the other.
You’re now set to dynamically change the variables you are pulling! Simply run your process for selecting relevant variables, build your XML strings through the Formula tool and pass them into your macro.
Have you ever used the Allocate tools and received back some strange looking variable names? You're not alone! The Allocate Rename Fields Macro will allow you to rename your fields into readable variables.
The macro can be downloaded here. Note: This will navigate you to the Alteryx Gallery. Select "Download & Install the Allocate Rename Fields Macro" and follow the prompts to install.
USING THE TOOL
The Allocate tools allow users to enrich their workflows with third party data provided from Experian and the US Census. This data contains demographic and household information by geography. Allocate tools can be found under the “Demographic Analysis” tab in the Alteryx toolbar; they include the Allocate Input, Allocate Append, Allocate Report, and Allocate Metainfo.
Allocate Input and Allocate Append tools allow users to select variables to display by geography. Once configured, the fields returned look something like this:
Add the Allocate Rename macro after the Allocate Input/Append. In the Configuration window, select the Dataset that you are pulling from. Press Run for the magic!
Voila! Your field names are now human-readable.
What if my company blocks access to downloading new tools/macros from the Gallery?
In the case that you cannot download this macro, you can use Alteryx to dynamically rename the field names. See is it possible to get the variable name I see in the Allocate tool?
Have some Latitude/Longitude points and not much else? Working with Spatial Objects and need more information than a simple map point? Time to call in the Reverse Geocoding macro. Reverse Geocoding can give some robust information to help make important business decisions when working with spatial data. Case in point: Your “friend” has hacked the Pokémon Go APK and hands you a list of Pokémon with the associated Latitude/Longitude. You can’t make an informed decision on your next weekend Poke-session without first understanding more than just the location on a map!
It is now easy to update your variable names using the Dynamic Rename tool and the MetaInfo. First, on the Allocate MetaInfo tool, you will want to select the data you are using and leave the Variables selected: Next, bring in a Dynamic Rename tool, select the Take a Field Names from Right Input Rows option (have your MetaInfo come in on the Right). For Old Field Name from Column choose Name, and for New Field Name from Column choose Description. I would recommend also choosing Ignore for if number of Field Names do not match. What the tool is doing is matching the data in the Name column of the MetaInfo and if it matches any of the names from the left Input data, it will replace it with the data in the Description column of our MetaInfo. I choose ignore because the tool will try to match all variables in the MetaInfo data set to the left input data, and throw a warning or error if it doesn't match. Below is an example of what the process would look like. It's also been configured into a macro that can be downloaded below: The configuration of the macro is simple, just pick the data set you are using (this should automatically populate with what you have loaded on your computer): Below is an example of how your module would look using the macro, it would need to be place somewhere below your Allocate tool to work: After using the macro (or process) the variables will appear as the description (which is what is shown in the Allocate tools):