Howto: URL encoding in QlikView and Qlik Sense

    There is currently no built-in support for URL encoding in QV or Sense, but it's actually very easy to achieve.

     

    After years of running into issues getting data from various online services (using http://... calls), with queries failing or being hard to get right due to incorrectly encoded URL parameters, I realised it's as simple as using the MapSubstring function together with a mapping table created from suitable online HTML tutorial, wikipedia etc - there are lots of sources for the character to URL encoding mapping.

     

    Duh! Better late than never though...

     

     

    The code is very simple:

     

    // Create mapping table for conversion from utf8 to URL encoded

    URL_EncodingMap:

    Mapping LOAD

        Replace([Character], 'space', ' ') as ASCII_Character,

        //Text([From Windows-1252]) as URL_encoding,

        Text([From UTF-8]) as URL_encoding

    FROM

    [http://www.w3schools.com/tags/ref_urlencode.asp]

    (html, utf8, embedded labels, table is @1)

    ;

     

    let vMessage = 'Test string including special characters such as #/|\ÅÄÖ';

    trace $(vMessage);

    let vMessageNew = MapSubstring('URL_EncodingMap', '$(vMessage)');

    trace $(vMessageNew);

     

     

    This will give you

     

     

    Test string including special characters such as #/|\ÅÄÖ

     

    %54%65%73%74%20%73%74%72%69%6E%67%20%69%6E%63%6C%75%64%69%6E%67%20%73%70%65%63%69%61%6C%20%63%68%61%72%61%63%74%65%72%73%20%73%75%63%68%20%61%73%20%23%2F%7C%5C%C3%85%C3%84%C3%96

     

    The MapSubstring call can thus be used to get correctly formatted URL parameters.

     

    The code above relies on having an Internet connection when executed, this can be a potential failure reason if your Internet connection for some reason goes down. Better then to save the retrieved table to QVD once, then creating the mapping table from that QVD in all other load scripts.

     

    Full writeup here.

    GitHub repo here.