Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Stefan_dT
Contributor III
Contributor III

Converting a Numeric working Function to on a alphanumeric field

I got a function 

 

 

=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.

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

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

Labels (2)
1 Solution

Accepted Solutions
Stefan_dT
Contributor III
Contributor III
Author

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)

 

View solution in original post

1 Reply
Stefan_dT
Contributor III
Contributor III
Author

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)