Masking join key in Alteryx to reduce key length

[Trailduck]

Hi

In my Alteryx flow I have a lot of long join keys (typically combinations of multiple fields). Previously in Qlik I've been using the function autonumber which generates an ID per combination of fields (https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/CounterFun...).

Is there any function like this in Alteryx? I've tried MD5 hashing the join key, but 32 characters is still very long for 500M+ rows.

Example:

Row 1:

DATE + NAME + CATEGORY + SUB-CATEGORY = 1

Row 2:

DATE + NAME + CATEGORY + SUB-CATEGORY = 2

[messi007 · Accepted solution]

@Trailduck,

You can use charToInt to convert the MD5 to integer.

Please see below :

Attached the workflow,

Regards,

[MarqueeCrew · Accepted solution]

@Trailduck ,

@TonyaS  will be happy to see this solution post. I recommend that you explore AMP for your workflow.  You'll want to use a current version of amp.  

in your runtime configuration, turn AMP on.  Compare the final results. Look for performance gains.  The join will automatically run faster. No need to tune it. 

cheers,

 mark

[TonyaS]

Thanks Mark! Yes I am happy to see this. AMP will be even better with the 2022.1 Release that is coming soon. 

@Trailduck 

Here is some of our main informational content about AMP Engine to start with: 

AMP Engine webinar (32 minutes): https://community.alteryx.com/t5/Analytics/AMP-Engine-Discover-the-Untapped-Performance-of-Your-Anal...

https://help.alteryx.com/current/designer/alteryx-amp-engine

https://help.alteryx.com/current/designer/Alteryx-Engine-and-AMP-Main-Differences

https://help.alteryx.com/current/designer/AMP-Memory-Use

https://help.alteryx.com/current/designer/tool-use-amp

There is even more here if you want to learn about it in more detail:

https://community.alteryx.com/t5/Analytics/Accelerate-Your-Analytic-Processes-with-the-New-AMP-Engin...

https://community.alteryx.com/t5/Engine-Works/AMPlify-your-Workflows/ba-p/617590

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Best-practice-with-the-new-Multi-threa...

AlterEverything Podcast: https://community.alteryx.com/t5/Alter-Everything-Podcast/66-The-Alteryx-AMP-Engine-Explained/ba-p/5...

https://community.alteryx.com/t5/Engine-Works/AMP-Engine-Technical-Deep-Dive-Part-1-Why-AMP/ba-p/570...  

https://community.alteryx.com/t5/Engine-Works/AMP-Engine-Technical-Deep-Dive-Part-2-Key-concepts-of-... 

With 2022.1 there will be Server Best Practices documentation on how to configure Server to run with both original Engine and AMP.