Converting a Numeric working Function to on a alphanumeric field

[Stefan_dT]

I got a function Stigchel posted on the QlikView community.  Patric_Nordstrom took this further and applied it to a QlikSense in his "Top 10 Viz tricks - Qonnections 2019" app, that works perfectly on a numeric field (Order_ID).  I'm now trying to use the same functionality on a alphanumeric field (Site_Cd), but can't figure out how.

=subfield(Concat( {<OrderID={">$(=OrderID)"}>} DISTINCT OrderID,'@',OrderID),'@',1)

Needless to say, just swopping out Order_ID with Site_Cd doesn't work.  I've got a feeling the issue is with the {">$(=Site_Cd)"}.  Any suggestions?

Edit: Maybe just mention, Site_Cd comes in values like: CEN_4111, EAS_12701 etc.  So if someone can come up with a way assigning numeric values to it, that should work to.  The number of site_cd's differe from month to month, but we are roughly looking at about 14k unique values.

Stigchel post: https://community.qlik.com/t5/QlikView-App-Development/How-to-find-next-previous-value-of-the-field/...

Patric_Nordstrom post: https://community.qlik.com/t5/Qlik-Sense-Documents-Videos/Top-10-Viz-tricks-Qonnections-2019/ta-p/15...

[Stefan_dT · Accepted solution]

For anyone that might  stumble unto this post with a similar issue, herewith the solution

@Patric_Nordstrom  suggested that I "create a numeric version of Site_Cd with the sort you like, Site_Order.  Haven't tested but should work."

The standard I use, is *_Cd for alhpanumeric identifiers and *_Id for numeric identifiers , so using below code, I converted Site_Cd into Site_Id.

// Handling known anomolies in the data ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
,IF(Site_Cd ='NA' , 10000001,
IF(Site_Cd ='UNK' , 10000002,
IF(Site_Cd ='no_atoll_site_id' , 10000003,
IF(Site_Cd ='Site4119' , 10404119,
IF(Site_Cd ='NGA_Nikon_House_SC', 10600000,

// Converting the correct Site_Cd's ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
IF(SubField(Site_Cd,'_',1)='CEN', 101 & NUM(COALESCE(SubField(Site_Cd,'_',2),0),'00000'),
IF(SubField(Site_Cd,'_',1)='EAS', 102 & NUM(COALESCE(SubField(Site_Cd,'_',2),0),'00000'),
IF(SubField(Site_Cd,'_',1)='KZN', 103 & NUM(COALESCE(SubField(Site_Cd,'_',2),0),'00000'),
IF(SubField(Site_Cd,'_',1)='LIM', 104 & NUM(COALESCE(SubField(Site_Cd,'_',2),0),'00000'),
IF(SubField(Site_Cd,'_',1)='MPU', 105 & NUM(COALESCE(SubField(Site_Cd,'_',2),0),'00000'),
IF(SubField(Site_Cd,'_',1)='NGA', 106 & NUM(COALESCE(SubField(Site_Cd,'_',2),0),'00000'),
IF(SubField(Site_Cd,'_',1)='SGA', 107 & NUM(COALESCE(SubField(Site_Cd,'_',2),0),'00000'),
IF(SubField(Site_Cd,'_',1)='SGS', 108 & NUM(COALESCE(SubField(Site_Cd,'_',2),0),'00000'),
IF(SubField(Site_Cd,'_',1)='SGC', 109 & NUM(COALESCE(SubField(Site_Cd,'_',2),0),'00000'),
IF(SubField(Site_Cd,'_',1)='WES', 110 & NUM(COALESCE(SubField(Site_Cd,'_',2),0),'00000'), 10000004))))))))))))))) as "Site_Id"

And then I could simply replace OrderID with Site_Id for the navigation, but still select Site_Cd as per Patric's solution:

=subfield(Concat( {<Site_Id={">$(=Site_Id)"}>} DISTINCT Site_Cd,'@',Site_Id),'@',1)