Converting a Numeric working Function to on a alphanumeric field

Stefan_dT — Sat, 16 Nov 2024 18:21:49 GMT

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/td-p/831644

Patric_Nordstrom post: https://community.qlik.com/t5/Qlik-Sense-Documents-Videos/Top-10-Viz-tricks-Qonnections-2019/ta-p/1580174?_ga=2.242154310.537636640.1594360776-2126863481.1588836162

Re: Converting a Numeric working Function to on a alphanumeric field

Stefan_dT — Thu, 16 Jul 2020 14:45:13 GMT

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)

topic Converting a Numeric working Function to on a alphanumeric field in App Development

Converting a Numeric working Function to on a alphanumeric field

Re: Converting a Numeric working Function to on a alphanumeric field