Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
In a recent conversation with my Alteryx Account Executive, we were discussing things you could do with Alteryx, and to be fair, the list is enormous. Somehow the conversation took a turn into a challenge for me to get Alteryx to turn on a light. Not only is Alteryx able to do this, it is able to control the colour based on conditions; and coupled with Server or Scheduler it becomes a pseudo automation hub with massive possibility.
So, at this point you’re likely asking, “Why would I use Alteryx for that? Seems like a waste to me.” Well, to answer this question I should cover 2 things.
Firstly, why would you control hardware for real world applications in the first place? With any tool for that matter, isn’t home automation and the IOT for hobbyists at home? The answer to this one is using real world signals. Lighting especially has been done in Industry for a very long time. Think of a mechanical system with a light on its control box. If the light is red, then Houston we have a problem. If it's green, then all is well.
The second question is, “Why would you use Alteryx?” There are many other ways you could do this. The answer to this question is yes, there are many other ways to achieve this, however the reason we should use Alteryx is 2 fold: firstly it’s likely you have already connected to the data you want to put a traffic light over, and secondly you already have Alteryx in your ecosystem, so no need to get another product and the endless approvals from IT.
In this example, I will demonstrate the use of the LIFX globe, however there are many other globes that have this function and could be used. So in essence you have a Server or Designer in your ecosystem, the globe can be purchased off shelf and provided you have no firewall problems in your worksite, bingo you’re good to go with no outside help and only ~$50 investment. Pretty powerful now you think about it.
So what type of use cases could I use this for?
Graduated daily sales targets – as the sales go up during the day the globe slowly turns green (e.g., create a burning platform idea).
To demonstrate an issue with a process – in supply chain applications if a route is blocked or load is behind then display red.
To indicate that manually entered data feeds are not completed.
The list is endless really.
So, enough talk. How do we do it?
For my example, I did my proof of concept at home. I pulled data from my Home Solar system and coloured the light based on the power load currently in my house. The easy way I came up with was to convert a percentage into a Red Green Blue (RGB) value. RGB values are 1 to 255 values for red, green, and blue. They are common for usage with colours (especially in web design). So a bit of maths….. and just saying, I’m not a mathematician…Google is my tutor in this respect.
Say the load is currently 80% (L = .8). To get my RGB value I use this formula:
R: 255 * [L] = 204
G: 255 * (1 - [L]) = 51
B: 0
The reason blue is 0 is we want a graduated scale from green through yellow and into red, so there is no need for blue values. If you want to use a wider colour scale you will need to find a formula that suits your purpose.
That last colour is awful, but you get the picture.
So great, I have something to display. I got it into a variable colour range that suits my purpose. Now what? Well Alteryx can use Python, and this opens up significant opportunities. Python is typically used in Alteryx for data-centric functions, however it is a commonly used language for home automation and various other functions. I don’t want to re-invent the wheel, so off to Github to see if anyone has a Python library for LIFX, and BOOM: Lifxlan by ML Clark.
But this represented a hurdle: I found out that LIFX globes operate using a colour system called HSBK – Hue, Saturation, Brightness, and Kelvin. Well surely that can’t be hard? How wrong I was.
After some reading, I learnt that the Kelvin is not needed. It is more used for different coloured white light. So that still left me with the HSB (or HSV / HSL) in some instances. This is a group of formulas I found:
R' = R/255
G' = G/255
B' = B/255
Cmax = max(R', G', B')
Cmin = min(R', G', B')
Δ = Cmax - Cmin
Hue calculation:
Saturation calculation:
Value calculation:
V = Cmax
So yeah, a bit of light maths to do with my morning Sudoku. Really at this point I thought maybe it’s time to give up. However, I continued and found an article that broke the calculations down and I was able to handle them one by one.
Below are the formulas as I entered into Alteryx. These take the example from the link and get them into 1 – 65535 values used by LIFX:
R = [R]/255
G = [G]/255
B = [B]/255
CMAX = max(r,g,b)
CMIN = min(r,g,b)
Luminance = ([Cmax]+[Cmin])/2
Saturation = if [Luminance] > .5 then ([Cmax]-[Cmin])/(2.0-[Cmax]-[Cmin]) else ([Cmax]-[Cmin])/([Cmax]+[Cmin]) endif
Hue = if [R] = [Cmax] then ([G]-[B])/([Cmax]-[Cmin]) elseif [G] = [Cmax] then 2.0 + ([B]-[R])/([Cmax]-[Cmin]) else 4.0 + ([R]-[G])/([Cmax]-[Cmin]) endif
Hue = [Hue] * 60
Saturation = round(65535*[Saturation],1)
Brightness = Round((65535*[Cmax]),1)
Hue = Round(([Hue]/360)*65535,1)
Hue = if [Hue] > 65535 then 65535 else [Hue] endif
The only part left now is to push the values into a Python script and hope that the light works. You will see from the script, I specify a light. You can get the light names and group names using calls within the Lifxlan library or you can turn all networked LIFX globes on at once. I left some debugging prints in my script so I could confirm the right data was used. Here is my Python script:
# List all non-standard packages to be imported by your
# script here (only missing packages will be installed)
from ayx import Alteryx
from ayx import Package
Package.installPackages(['lifxlan'])
import lifxlan
from lifxlan import LifxLAN
collection = Alteryx.read("#1")
#print(collection)
lifx = LifxLAN()
print("Discovering lights...")
devices = lifx.get_lights()
print("Found {} lights:".format(len(devices)))
hue = collection.loc[0,'Hue']
saturation = collection.loc[0,'Saturation']
brightness = collection.loc[0,'Brightness']
light = collection.loc[0,'Light']
print("Hue:")
print(hue)
print("Saturation:")
print(saturation)
print("Brightness:")
print(brightness)
print("Light:")
print(light)
if light == "ALL":
lifx.set_color_all_lights([hue,saturation,brightness,0])
else:
Bulb = lifx.get_device_by_name(light)
if Bulb != None:
Bulb.set_color([hue,saturation,brightness,0])
else:
Group = lifx.get_devices_by_group(light)
Group.set_color([hue,saturation,brightness,0])
After running my script within an iterative macro with a 10 second wait time to simulate being on a Server the results where good.
So, in conclusion this project was extremely fun, and has some real world uses, but further to that it opens the doors on the possibilities that Alteryx has lurking beneath the surface. Having access to programming languages like Python and R allows you to do some extremely creative things that convert Alteryx from an analytics powerhouse into an operational powerhouse.
Perhaps you, too, have a creative way that you use Alteryx to engage with the operational world. Share it with the community so we can all benefit from it. Looking forward to hearing/reading your stories and I hope you have found this blog helpful.
