Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER

Convert Saleforce 15 Digit IDs to 18

Contributor III
Contributor III

Convert Saleforce 15 Digit IDs to 18

Attached is a QVW script containing a Sub-Routine which converts 15 digit case-sensitive Salesforce IDs into 18 digit case-insensitive IDs. Plus a demo with sample data showing how it works. 

Why would you ever need this?

  • If you use a lot of Salesforce data, sourced via the Salesforce connector or Apex Data Loader then you have 18 digit IDs 
  • When reports are exported they contain the 15 digit ID
  • Sometimes complex Salesforce reports are impractical or too difficult to replicate using the connector. 
  • Salesforce's Opportunity Influence linkages to campaigns are not exposed by the API / reachable via a SOQL statement

Hope you find this useful!

--- John McTurnan

//-----------------------------------------------------------------------------------------------------------------------------------

SUB-ROUTINE BY ITSELF (No Demo)

//-----------------------------------------------------------------------------------------------------------------------------------

//-----------------------------------------------------------------------------------------------------------------------------------

//-------- SUBROUTINE:  CONVERT SFDC 15 DIGIT KEY TO 18 -----------------------------------------------------------------------------

// Credits: 

// John McTurnan:  Conversion from to Qlikview scripting from Javascript (jmcturnan@gmail.com)

// Ron Hess:  author of the Javascript version (http://boards.developerforce.com/t5/user/viewprofilepage/user-id/198)

//-----------------------------------------------------------------------------------------------------------------------------------

//-----------------------------------------------------------------------------------------------------------------------------------

//-----------------------------------------------------------------------------------------------------------------------------------

Sub SFDC_ID_15_to_18 (v_SFDC_ID15) //

  Let v_SFDC_ID18 = v_SFDC_ID15;

  Let v_SFDC_ID15 = chr(39) & v_SFDC_ID15 & chr(39);

  Let v_Check_digit = chr(39) & 'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' & chr(39);

  Let v_LT = ORD('A');

  Let v_GT = ORD('Z');

IF len(v_SFDC_ID15) <> 17 THEN //======  Check if ID conforms to basic length.  Exit & Null if not OK.

  Let v_SFDC_ID18 = Null();

  Exit Sub;

EndIf;

  Set v_i = 1; 

    

   For v_i = 1 to 3

  

      Set v_X = 0;

      Let v_Block = chr(39) & Mid($(v_SFDC_ID15), 5 * ($(v_i) - 1) + 1, 5) & chr(39);

      v_j = 1;

     

      For v_j = 1 TO 5

     

         Let v_C = ORD(Mid($(v_Block), $(v_j), 1));

         IF v_LT <= v_C And v_C <= v_GT THEN

          Let v_X = $(v_X) + POW(2,($(v_j) - 1));

         EndIf;

      Next

      Let v_temp1 = Mid($(v_Check_digit), $(v_X) + 1, 1);

  

      Let v_SFDC_ID18 = v_SFDC_ID18 & v_temp1;

   

  Next

  Let v_SFDC_ID18 = chr(39) & v_SFDC_ID18 & chr(39);

EndSub;

//-----------------------------------------------------------------------------------------------------------------------------------

Attachments
Comments
Not applicable

John - This is really useful.

0 Likes
Creator II
Creator II

John:

I'm really thankful for this post, most of all, because you used Qlikview code.

Great job !.

CB.

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2013-12-20 05:06 PM
Updated by: