Debugging Alteryx Analytic Apps


Analytic Apps in Alteryx are great, they can either be hosted on an Alteryx Server with a web-based user interface which allows non-Alteryx users to run the workflow or a user with Designer can run them on their desktop without devling inside the workflow.

This post isn’t focused on what an Analytic App is or how to build them. If you want to know more about creating Analytic Apps then check out this Alteryx YouTube video.

I use Analytic Apps a lot as they are a way of having a user easily run a workflow on new data without fiddling around with the workflow to point the input tools at the new datasource. However, as I recently found out, a downside of running an app is you don’t get the benefit of running the workflow directly inside Designer to see what happens to the flow of your data (which is one of the reasons I love Alteryx).

For example, in Designer it is clear how many records are passing through each tool:

Capture.PNG

The Designer view of a workflow – it’s easy to see how the data flows through the workflow

However, when you run it as an analytic app it becomes a bit of a black box. Yes you can see the results window which tells you some of the information but you don’t get to see your data at each anchor like you do in Designer. So if something unexpected starts happening to your workflow it can tricky to workout why.

Capture2

Example of the output log from the Analytic App – not as easy to understand compared to the Designer view

Debugging mode to the rescue

So I need to admit now, that when Interface Designer window is loaded this big button called ‘Open Debug’ I’d never pressed before and while it’s obvious from the name it was only after a couple of hours of going ‘the workflow runs fine in Designer, I can’t work out why when it is run as an app that it goes wrong?‘ that I decided to press it and see what it does!

So the workflow that I discovered this on was the one below where I have lots of nested text input boxes and detour tools to run an API via different end points depending on the user’s inputs.

Capture3

Workflow in Designer with Interface Tools making this an Analytic App

Analytic App.gif

Example of nested questions within an Analytic App

I was finding that I could run the workflow in Designer fine, and I would manually update the direction of the detour tools to run each branch of the workflow and hence check each API call was working correctly. Yet when I tried to do the same in the App Interface I was getting errors as the API calls were not being correctly formed, which is when I turned to Debug mode.

Debug mode is activated in the Interface Designer. First press on the magic wand icon to load up this screen (below) and then complete the Analytic App with the data you want, then click on the ‘Open Debug’ button to the right.

Debug1

Steps to set up an Analytic App into debug mode

This then opens up a new workflow with the settings from the App detailed at the top of the workflow.

Debug2.PNG

A new workflow with the settings from the Analytic App passed to it

You can now run the workflow and see the data running through the workflow to help understand where the Analytic App is (or in my case not) updating a specific value and flowing through the workflow.

Debug3

Debug mode allows you to see how the data flows through Designer so you can check the App is working as expected.

As you can see Company X is now passed into the workflow in the q field which was what I selected in the Analytic App so i know it is working as expected and can now save myself many headaches in the future!!!

Advertisements
Posted in Uncategorized | Leave a comment

Making Alteryx workflows more re-usable


How often have you looked at a set of data know what you need to do to transform it and gone straight in to build an Alteryx workflow which works for the data you currently have, but then find a week, month, year later that you’ve got a new extract of the data but the workflow didn’t work as intended? Now Alteryx is great when faced with this problem as you can easily work your way along the tools to identify where the workflow failed and update it accordingly.  

But one thing I am trying to get better at, and also teach others, is to use a different choice of tools to make the workflow more reusable and able to host on server without needing constant debugging.

To show an example of how a change in the tool selection can help make a workflow more usable let us look at it in terms of the following problem.

Problem:

You’ve been presented with a text file where the data needs cleaning up.  Due to they way in which the user inputs the data, the same records may be split over several rows.

First approach using Alteryx:

If we look at the input data we can see that there are four lines where the data is split at the same point, and a fifth line where the data is split over three lines.

Input data

Untitled drawing1

Untitled drawing2

Now we could bring the data straight in and use the tab delimiter to parse the data into the correct columns, however by doing this we would lose important information on the incomplete lines which would help us at a later stage to clean up the file.  So instead we first bring the file in without parsing it on the tab delimiter.

Untitled drawing3

Then we can use a formula tool to count the number of tab delimiters in the field (Field_1).

Untitled drawing4

There is a useful Regex formula for this:

REGEX_CountMatches([Field_1], “\t”)

The result of this formula is a new column with a count of the number of tab delimiters for each record. Now I won’t go through in detail the other steps, but essentially what you do is check each record to check if it has the correct number of delimiters and mark that record as complete, otherwise do a running total of the delimiters until it matches the total, then concatenate the records using the summarise tool before using the text to columns tool to parse out the now complete records on the tab delimiter.

Untitled drawing5
Issues with this approach

Now here lies the problem, by using the text to columns tool you need to specify the number of columns to split on, which in this case is fine as I know from the Regex_CountMatches formula how many columns to split on. 

But what happens when I have more than 18 (18 columns of data but 17 delimiters)? This would require the user to go into the workflow and manually update it in two places:

  1. In multi-row formula tool where the value to calculation has a hard-coded valueUntitled drawing6
  2. In the Text to columns tool to update the number of columns to split onUntitled drawing7

So how can the workflow be made more dynamic / reusable?

Now we want the workflow to be usable for a different version of the file, for example one with more columns, so how can the existing workflow be tweaked to remove the need for the user to manually update.

The first area is around finding the maximum number of delimiters for the records and using this as a value in the calculation.

This is done by using the summarise tool to return the maximum value for the count of delimiters, and appending this value to the dataset.

Untitled drawing8

This means the multi-row formula can be updated to replace the hardcoded value with [Max_Num_Delim].

Untitled drawing9

Then secondly the next change is in how the text to columns tool is applied. Instead of splitting the data to columns, we chose the option of ‘Split to rows’, which negates the need for knowing how many columns are in your data.

Untitled drawing10

Adding a few steps to to count the number of row created for each record and Cross Tabbing the data (column number as the header) and grouping on the Record ID now gets us the data into the same shape we require without any hardcoding of numbers and a more reusable workflow.

Untitled drawing11

I have published the two solutions to this problem on my github page:

Traditional approach:

https://github.com/CG003/Alteryx/blob/master/Client_CL%20cleanup%20(github).yxmd

Dynamic approach:

https://github.com/CG003/Alteryx/blob/master/Client_CL%20cleanupv2%20(github).yxmd

 

Posted in Alteryx, Tips, Uncategorized | Leave a comment

Brighton Marathon


So after finishing Paris marathon in a new PB of 3:28:05, I was looking forward to getting back out and doing another Marathon – I guess it’s good that I actually look forward to this, as I’ve still got another one to do!

I’ve had this past week following Paris off work (except for a couple of days at college for study) as it was mine and my wife’s birthdays. We enjoyed a spa day on Friday to relax, recover and chill out some more.

Training wise I did a 1500m swim (bit of practice before a triathlon in May), and Dulwich parkrun on Saturday to celebrate it’s first birthday.

We travelled down to Brighton on Saturday, as with another expo and early start it was the best option. At the expo I chatted with Dave @compressport who was friendly at VLM last year when I first met him, and picked up a new pair of Trail Compression shorts.

I use their socks to run with and F-legs for post run recovery, but also have the ForQuads, which I haven’t got on with running wise. I find they slip down, and I lose too much time having to pull them up. But they work really well under a tri suit or cycling shorts. When chatting to Dave we think it might be because of my hairy legs and the silicon not gripping. So to get round this I am giving the new trail shorts a go. First impressions are that they offer very good compression of the quads and hamstrings, during today’s run I never got a feeling like my hamstring would cramp (I did feel like it might go in Paris, so kept to a pace which didn’t risk it).

With the forecast for Brighton being a scorcher but with a strong southerly breeze, it was difficult to gauge what impact this might have. Following running Paris at an average of 4:56/km I set my Garmin to 4:55/km. Given the course up to 15km is undulating, with hindsight, I feel that this was too optimistic. Even though I felt fresh at half marathon (1:43:43), 1 minute up on target. The extra effort at the start might have influenced my finish.

Getting back on the sea front, the wind didn’t seem to be having too much impact, and was acting as a nice cooling breeze. Heading up to the out and back loop in Hove I decided to do a watch reset to wipe the 1 minute gained so far and just settle into a 4:55/km pace and bank the minute and get through the next 10km or so at a steady pace. This bit of the course is one of the mentally challenging sections. There is a slight up hill to the turn around, and is through a residential area so no sights to aim for.

After the residential section it was back onto the seafront out to Portslade. The long straight road and the power station in the distance make this another challenging stretch. I got past the 20mile point and still felt relatively good, but at 35km the wheels fell off. This chart from Strava, shows it quite well:

20130414-182549.jpg

I can’t really fathom what happened next, I think recording a > 5minute km and starting to feel a bit of stomach pain from the gels made me walk a small section. But walking is a marathon runners nemesis. Psychologically, when I know I am going to miss my target time then re-starting, plus knowing there is still about 30 minutes to race – I give up. I also think I under estimated what impact having a marathon from last weekend still in my legs would have.

The last 6km took was a mix of run/walk. The crowd really push you on, and give a little cheer if you start re-running in response to their shouts. But when you get out of their sight it is all too easy to walk again. During one of my walking stints I noticed another runner with Paris-Brighton-London on the back of their vests. I just had to catch them up to meet someone who has probably had their friends call them mad, just like mine have!

Crossing the finish line was a relief and putting it all into perspective a 3:38:33 is still an amazing time, off the back of a PB last weekend, and is my 3rd fastest marathon time. After crossing the line, you end up having to walk past all the baggage lorries which in Paris they let you exit before. This walk seemed to go on forever.

NACC one of the charities I am supporting had a tent on the beach, and it was good to meet the team and have a well earned sit down. So far between my own page and my shared work fundraising page I have raised over £700, so thank you once again to those who have sponsored me.

20130414-184850.jpg

So with one marathon to go, I’m going to have a bit of a different recovery. Lots of pasta to refuel properly, and sitting at my desk as I’m back to work (this isn’t good for recovery). I will also try and do some swims and ride to work.

I am looking forward to London next week and will try to get back under 3:30 and add to this medal collection.

20130414-190542.jpg

After getting home from the race it was sad to hear the tragic news of a fellow runner in the marathon today. It always puts life in to perspective, and to make the most of each day. RIP.

Posted in races | Tagged , | Leave a comment

Paris Marathon


So I am back from a weekend away in Paris with Katie for my first marathon in the #3marathons3weekends challenge.

20130408-210058.jpg

We arrived mid-afternoon in, a slightly warmer than London, Paris. After a bit off a faff on the metro to get to the hotel, we were welcomed with a room with a view!

20130408-210359.jpg
Then we headed off to the Expo to collect the race number and goodie bag. The Expo, like heading off to Excel for the London marathon, is a bit of an annoyance when you would rather be off your feet, but also adds to the excitement of race day. One nice touch at the Paris marathon is getting your name printed on the bib number. And it worked as I got a few shouts of “Super Chris” (in French accent) on race day which helped spur me on.

20130408-211003.jpg

20130408-211025.jpg
After a pasta meal for a bit of carb loading, it was time to get some sleep for race day.

On the morning of the race, I made sure I got a decent breakfast (2 slices of strawberry jam toast, chocolate curls cereal and a mini pain aux raisin) it was time to head off to the Champs Elysese for the start. I was in the yellow start for the 3h15 a 3h30 runners. So in this pen I didn’t want to push too close to the front. The mistake I have always made in my 3 previous marathons is setting off too fast 3h15 – 3h20 pace and then paying for it at mile 20.

20130408-211606.jpg

20130408-211620.jpg
At Paris they do something different to the start in London. After firing the cannon for the elite start, there was no movement from our pen. It took about 5 minutes for them to open the pen, and we then walked forward to the start line where we were held again for a few minutes before being released onto the 42.195km course. The Champs Elysese is nice and wide so you can set off at your desired pace straight away instead of fighting for position and being stuck behind runners who shouldn’t be that close to the start!

The early start meant that it wasn’t that warm at the start, but also meant that the first 5km were I’m shade so pretty chilly. But as the road wound its way through the city and out to the first parkland it soon warmed up. I needed to ditch the extra top I was wearing at 12km as I worried it might cause over-sweating and hence cramping later on.
Following mis-pacing previous marathons I was concentrating on following the pace guide on my Garmin and sticking closely to the 4:59min/km target pace. I stuck with this fairly well upto the 16km point, gaining only a minute, so re-adjusted my target pace to 4:56, and hence zeroing the one minute gain. I saw Katie at the 27km point and was feeling good and well on for my target, but at the back of my mind knew there was still a long way to go, and another few km to the magical 20 mile point.

20130408-212945.jpg
Getting to the 20mile point I knew I could do it, and it added to it when I was passing people at this point who were probably feeling like I have before and succumbed to walking. But I was sensible not to up my pace and just bring it home, otherwise I could have been just like them in 500m!

Going through the park by Long Champ race course was a different experience to London, as there weren’t that many spectators, unlike the stretch from the Tower of London to the finish which is 3+ deep.

Getting to the 37km point I knew it was just a parkrun to go. I could easily visualise this as just 3 laps of Dulwich Park, but a 25 minute run instead of the 19 minutes I usually do it in.

I saw Katie again just before the 42km mark, and picked up the pace from here as by looking at my watch I knew I would run under 3h30 and even if I was to cramp I would have enough time to walk/hobble in.

20130408-213732.jpg

20130408-213749.jpg
I did it!!

So 1 marathon down and 2 to go. Over all I think I have got the right mileage in the bank and Paris marathon’s pace wasn’t too difficult, so I just need to recover correctly this week (helped by my compression leggings) to be ready for Brighton.

20130408-214040.jpg
My Garmin recorded the course as 42.4km but that is expected with the tunnels and having to do a bit of weaving so not following the blue line exactly. Therefore I have an average pace of 4:54/km to set my Garmin to in Brighton, with the plan to stick to this pace to the 20 mile point then see what happens…

Also a massive thank you to everyone who has sponsored me so far, I’ve broken through the £500 mark. All your support has not only motivated me throughout the long training runs but also helps my chosen charities.

Posted in races | Tagged | Leave a comment

3 marathons in 3 weekends in April all in sub3h30??


Ok so where do I start? After finishing London marathon last year, and just missing out on my target time of 3h30m, I got a message from Fullers London Pride (one of the sponsors), that I had won their Facebook competition and got a free place in the 2013 VLM.

Most people would be happy with that, but with a bit of a wish to one day do an ultra marathon (anything at least 1 mile more than a marathon, but I would personally class it as 50miles+) I decided on a slightly bonkers challenge. Knowing that both Paris and Brighton marathons have a first come, first served entry procedure, so I knew following confirmation of London doing 3 marathons in 3 consecutive weekends was feasibly possible. To add a bit of challenge to it, I want to try and do them all I’m sub 3h30.

I am using the twitter hash tag #3marathons3weekends to update my progress.

Posted in races | Tagged | Leave a comment

First 8 weeks o…


First 8 weeks of marathon training

w/c           Runs Distance (km) Time      Ave Speed (km/h) Ave HR (bmp) 
20/02/2012 6      62.08             5:27:20  11.4                    148 
13/02/2012 4      49.63             4:08:43  12.0                    152 
06/02/2012 5      53.57             4:44:50  11.3                    150 
30/01/2012 5      33.88             2:53:51  11.7                    150
23/01/2012 6      42.03             3:43:12  11.3                    146 
16/01/2012 5      40.33             3:31:31  11.4                    150 
09/01/2012 5      48.35             4:25:39  10.9                    145 
02/01/2012 6      42.75             3:56:43  10.8                    145 

Total        42    372.62           32:51:50  11.3                    148 

 

 

 

Aside | Posted on by | Leave a comment

Marathon training run 26 / Brockwell Parkrun


Originally I hadn’t planned on doing Brockwell parkrun as today I was supposed to do Watford Half Marathon, however with the over night forecast as it was I decided to do the Parkrun just to maintain my 5 runs per week. Not sure how it happened but I missed the start by approximately 2 minutes, which had an effect on my run, as I definitely run quicker when there are similar placed runners around. I finished in 20:18 ‘ish. And then ran a bit past the finish so the Garmin would actually record 5km instead of 4.96km, but Strava still didn’t record it as a 5km best effort 😦
But given all that I was pleased with my time, as I shouldn’t have raced that fast just in case the snow didn’t come and I’d have been able to race Watford.
So I might head out in a bit to try and get a 13 mile run in, but the snow hasn’t melted.

Here’s a photo from the brockwell Flickr photo stream.

20120205-120214.jpg

Posted in training | Tagged , , , | Leave a comment