getCustomProp.qvs

LET v_http_header_key = 'runas';  // set according to your Virtual Proxy
LET v_http_header_val = 'script'; // set according to your Virtual Proxy

SUB getCustomProp (param_LibConnection, param_custPropName, param_varPrefix, param_breakIfMissing, param_KeepQrsRepsonseTable);
    TRACE [Calling SUB getCustomProp(...,'$(param_custPropName)','$(param_varPrefix)',$(param_breakIfMissing),$(param_KeepQrsRepsonseTable))];

    // This sub queries the QRS API to get the custom properties of the current application and
    // puts them into a variable (or variables, since wildcards are possible it can do all at once).
    
    // To setup the virtual proxy follow these instructions: 
    // https://community.qlik.com/t5/Qlik-Sense-Documents/How-to-access-QRS-Repository-from-Load-Script/ta-p/1484264
    
    // The sub needs the following parameters:
    
    // 1) the name of a http-GET REST Connection (defined in your data connections)
    //    note that you also have to set two global variables outside the script to
    //    define the http-header key and value for authentication
    
    // 2) The name or pattern (wildcards allowed!) to look for in the custom property namespace
    
    // 3) a possible prefix (typically 'v') for the variable name (the variable name matches the 
    //    custom property name)
    
    // 4) set to 1 if your script MUST assign the given variable. If such a custom property doesn't 
    //    exist on this application, it breaks the script execution
    
    // 5) set to 1 to leave the tmp_CustProps table in the data model, either because you're curious 
    //    or because you plan to call this sub multiple times and want to save the time to query 
    //    the QRS API again (the result would be the same a second later ;-)
    
    // Christof Schwarz, 18-Dec-2020
    
    // Examples to call this sub:

    // Set explicitly custom properties "namespace" and "ShowInMashup" to a variable "namespace" and
    // "vShowInMashup". Break if not found. Don't drop REST response table until the last call.
    // CALL getCustomProp ('REST GET (vm1_christof.schwarz)', 'namespace', '', 1, 1);   
    // CALL getCustomProp ('REST GET (vm1_christof.schwarz)', 'ShowInMashup', 'v', 1, 0); 

    // Turn all Custom Properties into variables with a prefix "v". Continue, even
    // if no Custom Property is set.
    // CALL getCustomProp ('$(vLibRestGET)', '*', 'v', 0, 0);

    LET v_tmp_XrfKey = 'databridge' & Left(PurgeChar(Repeat(Rand(),3),'.,'),6);
    LET v_tmp_ThisAppId = DocumentName();
  
    IF IsNull(TableNumber('tmp_CustProps')) THEN

        LIB CONNECT TO '$(param_LibConnection)';
        tmp_QrsResponse:
        SQL SELECT 
            "__KEY_root",
            (SELECT 
                "value" AS "customProp.value",
                "__KEY_customProperties",
                "__FK_customProperties",
                (SELECT 
                    "name" AS "customProp.name",
                    "__KEY_definition",
                    "__FK_definition"
                FROM "definition" PK "__KEY_definition" FK "__FK_definition")
            FROM "customProperties" PK "__KEY_customProperties" FK "__FK_customProperties")
        FROM JSON (wrap on) "root" PK "__KEY_root"
        WITH CONNECTION (
            URL "$(vQRSAPIurl)/app/full",
            QUERY "filter" "id eq $(v_tmp_ThisAppId)",
            QUERY "xrfkey" "$(v_tmp_XrfKey)",
            HTTPHEADER "X-Qlik-Xrfkey" "$(v_tmp_XrfKey)",
            HTTPHEADER "$(v_http_header_key)" "$(v_http_header_val)"
        );
        DISCONNECT;

        tmp_CustProps0:
        LOAD
            customProp.name,
            __FK_definition AS __KEY_customProperties
        RESIDENT tmp_QrsResponse
        WHERE NOT IsNull(__FK_definition);

        LEFT JOIN
        LOAD
            customProp.value,
            __KEY_customProperties
        RESIDENT tmp_QrsResponse
        WHERE NOT IsNull(__FK_customProperties);
        DROP TABLE tmp_QrsResponse;

        // in case of multiple values assigned to a custom property, concat them into a pipe-separated list
        tmp_CustProps:
        NOCONCATENATE LOAD 
            customProp.name,
            Concat(customProp.value, '|') AS customProp.value
        RESIDENT 
            tmp_CustProps0
        GROUP BY
            customProp.name;

        DROP TABLE tmp_CustProps0;
    ELSE
        TRACE [Table "tmp_CustProps" found in data model from previous call.];
    END IF

    LET v_tmp_Found = 0;
    FOR v_tmp_Row = 1 TO NoOfRows('tmp_CustProps')
        LET v_tmp_pName = Peek('customProp.name', v_tmp_Row -1, 'tmp_CustProps');
        IF v_tmp_pName LIKE param_custPropName THEN
            LET [$(param_varPrefix)$(v_tmp_pName)] = Peek('customProp.value', v_tmp_Row -1, 'tmp_CustProps');
            TRACE [Variable "$(param_varPrefix)$(v_tmp_pName)" assigned from CustomProperty.];
            LET v_tmp_Found = v_tmp_Found + 1;
        END IF
    NEXT v_tmp_Row
    TRACE [$(v_tmp_Found) variables assigned.];

    // remove temp variables
    LET v_tmp_Row = Null();
    LET v_tmp_XrfKey = Null();
    LET v_tmp_ThisAppId = Null();

    IF v_tmp_Found = 0 AND Alt('$(param_breakIfMissing)', 0) THEN
        // remove temp variable
        LET v_tmp_Found = Null();
        [Error: Could not find custom property matching "$(param_custPropName)"];
    END IF

    LET v_tmp_Found = Null();

    IF Alt('$(param_KeepQrsRepsonseTable)', 0) THEN 
        TRACE [Keeping table "tmp_CustProps" in data model.];
    ELSE
        TRACE [Dropping table "tmp_CustProps" from data model.];
        DROP TABLE tmp_CustProps;
    END IF

END SUB