FormulaAddIn (or 3Rd Floor Flat part 2)

Well the next question from my 3Rd Floor Flat user was "So do I need to use that big long formula every time I want to title case something?" and of course for a common task for a user that's not ideal.  First thought was to wrap it in a macro, but that feels like overkill for a simple task.  Then I remembered something I had seen in the Software Developer's Kit - FormulaAddIns!

Did you know it is possible to create your own functions to appear in the formula tool?  And what's more for ones which can be built using existing functions it's really quite easy.

For the lack of a better name I've called my new function TITLECASE2 and if you create the folder

C:\Program Files\SRC\Alteryx5.0\RuntimeData\FormulaAddIn\

and save the below xml file into it,

then restart Alteryx you will find TITLECASE2 appears right there in the function list under TITLECASE; giving me the elegant solution I was looking for.  I think the xml is fairly self explanatory, but let me know if you have any questions on it.

3Rd Floor Flat

Should TitleCase("3rd floor flat") resolve to 3Rd Floor Flat? Alteryx thinks it does, I'm not so sure...  But the question I had from a user on Friday was how do I turn "3rd floor flat" into "3rd Floor Flat"?  Which of course is what you would want on an address envelope. 

My first solution involved using a regex tool to pull the first letters out then the dynamic formula macro to build the string back together captilasing the first letters.  I wasn't overly happy with the solution though  (far too many tools for a simple task) so after some more thinking have come up with the following formula:

REGEX_REPLACE(TitleCase(REGEX_REPLACE([Field1], "(\d)", "$1X")), "(\d)X", "$1")

(which I think should always work...)

Anyone think they can come up with a more elegant solution?

Dynamic Formula

A recent post on the Alteryx forum from Stephs (here) got me thinking: wouldn't it be useful if there was a Dynamic formula tool?  I think this would solve Stephs problem in that they could build formulas (with the formula tool) to compare the fields and use an if statement to choose between the file and database version and then push the data back to the database (that's if I've understood the problem correctly, if not I've already thought of some other great uses for this tool.).

Well the motto of any advanced user of Alteryx should be "If you want a new tool in Alteryx, build it!" So here it is:



Just drop all the files in the zip into your macros folder. Usually something like C:\Program Files\SRC\Alteryx5.0\RuntimeData\Macros\.

The two interesting features of this macro are :

• It's the first time I have made use of the macro action "Update Raw XML", which worked very nicely.
• I have used the batch macro tool in a way it wasn't particularly designed for.  I used it to run a single iteration which you might think defeats the point of the 'batch' feature, but it allows you to take data from the data stream and insert into the properties of the formula tool.  Using this technique, any Alteryx tool could be made to have dynamic properties!  I'm sure there must be some more uses out there for this technique.

Now for one of those other uses I've thought of.  Remember this query from the Alteryx Team blog here?

Hi Tara,
I have a table with 60+ fields. I need to create an extra field that is the total of all the 60+ fields. So far, I've been doing this with the formula tool and writing an expression as follows "[01]+[02]+[03]+[04]+[05]+[06]+[07]+[08]+[09]+[10]+[11]+[12]+[13]+[14]+[15]….. etc". Just wondering if there is a quicker way to do this? If not, can you add as a suggestion for a future release?
Thanks,

Well with the dynamic formula tool you can write this formula dynamically!  Plus it becomes very easy to also create subtotals based of the field names.  Check out the below module showing how this could work:



If anyone else thinks of some other uses for this tool then add a comment here or drop me a note.

Street View Macro

Following on from Ron's great post about using HTMLPassThrough here.  I would like to share with you all the Street View macro which came out of the same brainstorm in the solution centre at Extend 2010.

What it does is allow you to insert a Google Street View iframe into an html report from a given set of x, y co-ordinates without needing to know about HTMLPassThrough or indeed worrying about it. It uses a couple of interesting techniques including HTMLPassThrough and batch macros. I'm not going to go into huge amounts of details of how it works here, the modules are commented so just let me know if you have any specific questions.

So here it is:



Just drop all the files in the zip into your macros folder. Usually something like C:\Program Files\SRC\Alteryx5.0\RuntimeData\Macros\.

I've also created an amended version of the presidents report sample here:



Extract this one to here to see it work: C:\Program Files\SRC\Alteryx5.0\RuntimeData\Samples\Reporting Samples\

Only caveats I'm going to throw in for this one is that it is html reports only (it's an iframe after all) and if you are going to use it make sure you do so within Google's licence agreement.