Mind the Gap: Why BETWEEN is the Key to Taming Data Chaos in Alteryx

 

The JOIN tool  has always been a bit of a hero. It’s one of those trusty old tools that has made blending data easier and more accessible for all of us. Before it came along, merging datasets often felt like a task for only the most experienced data wizards. But then the JOIN tool arrived, and suddenly, data blending wasn’t so intimidating anymore. It allowed anyone, no matter their skill level, to combine datasets with ease, almost like putting together the pieces of a simple puzzle.

But even heroes have their quirks. For all the JOIN tool’s strengths, there’s still a need for some extra guidance to get it to behave like the SQL joins many of us know and love. And, oh, the fun really begins when you step into the world of in-database (in-DB) joins. Here, things start to get a little more complex, and you might find yourself missing the precision of ANSI SQL joins.

This is where the BETWEEN condition comes in. It’s like a gentle nudge, guiding your data to fit together just right. BETWEEN helps you set boundaries, making sure that when you join data based on ranges—like dates or numbers—you get exactly what you need, no more, no less. It’s like finding the perfect fit for a missing piece in your data puzzle.

So, while the JOIN tool in Alteryx is wonderful, remember that BETWEEN can help bring a little more order to the chaos. It fills in the gaps and makes sure your data relationships are just as they should be.

---

ENSO: A New Approach to Joining Data

While Alteryx’s JOIN tool provides a powerful foundation for data blending, there’s another player in the field that’s taking the concept of joins to the next level—Enso. Enso’s approach to joining data addresses some of the limitations that users might encounter when relying solely on traditional JOIN tools.

One standout feature in Enso is its ability to perform joins with case insensitivity. This may seem like a minor detail, but anyone who’s wrestled with data inconsistencies due to case differences knows how significant this can be. In Enso, you don’t need to worry about whether your data is uppercase, lowercase, or a mix of both—the platform handles it seamlessly, ensuring that your joins are accurate and complete.

Enso also simplifies the implementation of the BETWEEN condition, providing it as a built-in feature within their JOIN component. This makes it easier to define ranges and boundaries within your data without the need for complex workarounds. The inclusion of BETWEEN functionality right within the JOIN component aligns more closely with SQL-style joins, giving users a more intuitive and familiar experience when working with range-based data relationships.

But Enso doesn't stop there. Its JOIN component goes a step further by displaying configuration options in a way that is both transparent and accessible. This not only aids in understanding how your joins are being performed but also facilitates the programmatic substitution of join keys and criteria. This flexibility is particularly useful in dynamic data environments where the conditions for joining datasets may need to change frequently.

In contrast to Alteryx, where you might need multiple tools or custom formulas to achieve the same result, Enso streamlines the process. The programmatic capabilities of Enso allow for a more automated and adaptable approach to data joins, reducing the potential for error and saving valuable time.

As data environments grow more complex, the tools we use must evolve to keep pace. Enso’s enhancements to the traditional join process—case insensitivity, integrated BETWEEN functionality, and programmatic substitution—represent a significant step forward in managing and blending data with precision. For those looking to reduce data chaos and improve the accuracy of their joins, Enso offers a compelling alternative to consider.

Why This Matters to CReW

So why am I sharing this on the CReW blog? It’s simple: ENSO is about to make a big splash, and it’s something you should have on your radar.

Adam Riley, a name familiar to many of you, has been working at ENSO for over a year now. During this time, ENSO has been fine-tuning its offering, and the big news is that the software is moving to General Availability (G.A.) this September at Big Data LDN (September 18-19). What’s even more exciting is that both free and paid versions of ENSO will be available, giving everyone the opportunity to test out this next-generation tool and see what 2024 magic comes with it.

I’ve had the chance to play around with ENSO myself and provide feedback directly to the team. One area where I provided input was on the implementation of BETWEEN logic. During this process, I pointed them to a little-known CReW macro, CReW Generate Date Rows, which helps to solve the BETWEEN problem for date/time fields in Alteryx.

Getting Between in Alteryx or CReW

Interestingly, I never moved this macro to marketplace.alteryx.com. Why? Because I was hoping that the functionality would eventually be added to the main Alteryx product for all data types—or at the very least, that someone would ask me to move it there. But the silence was deafening.

So here’s my suggestion: If you want to see real JOIN love and updates to Alteryx, make your voice heard at community.alteryx.com. The community has always been a powerful force for change and innovation, and your feedback can drive the improvements we all want to see.

But if you’re ready to see that love in action today, try out ENSO. Join their community at community.ensoanalytics.com and see what Adam’s been up to. I think you’ll be impressed with what ENSO has to offer, and it might just be the tool that transforms the way you approach data blending in 2024 and beyond.