Using NPrinting API in QlikSense - GET Filters

[tm_burgers]

I am trying to get the table with all my current filters and their values. 

I have already gotten to the point of pulling all my users, and their corresponding filter selections, but getting the values of those filters is eluding me. 

Does anyone have a sample script for pulling the nested fields / values tables from the filters data?

RestConnectionMasterTable:
SQL SELECT
"__KEY_data",
(SELECT
	"id" ,
	"name",
    "description",
    "enabled",
    "appId",
    "connectionId"
FROM "items" FK "__FK_items")
FROM JSON (wrap off) "data" PK "__KEY_data"
WITH CONNECTION( URL "https://qlik-nprint.mycompany.cloud:4993/api/v1/filters", HTTPHEADER "cookie" "$(vCookie)",QUERY "Limit" "10000") );


NPrint_Filters:
NoConcatenate LOAD 
*
RESIDENT RestConnectionMasterTable;
drop table RestConnectionMasterTable;

[Ruggero_Piccoli]

Hi,

Instead of running direct SQL that could be dangerous you could use:

- the Governance Dashboard https://community.qlik.com/t5/Qlik-NPrinting-Documents/NPrinting-Governance-Dashboard/ta-p/1744538

- the official APIs https://help.qlik.com/en-US/nprinting/November2020/Content/NPrinting/Extending/Intro-Extending.htm for example https://help.qlik.com/en-US/nprinting/November2020/APIs/NP+API/index.html?page=58

Best Regards,

Ruggero

[tm_burgers]

Thanks Ruggero, 

Unfortunately I have not been able to troubleshoot my connection issues to the postgres server. But my REST API connection is working. 

The governance dashboard does not give enough details to run a full audit of filters to ensure that all are structured equally to give consistent results. I have the full data model working using the REST api, but getting the details from each filter is giving me difficulty. 

[Ruggero_Piccoli]

Hi,

Could you customize the governance dashboard to met your requirements? In case you have questions about it you could ask on its community conversation.

If you need also to monitor the activities after execution, you can consider the audit trail https://help.qlik.com/en-US/nprinting/November2020/Content/NPrinting/AdministeringQVNprinting/Audit-... that will be available via API from February 2021.

Please note that the structure of the repository could change in future versions (APIs have a system to manage the compatibility) so it could also be that you will be forced to update the solution.

Best Regards,

Ruggero

[tm_burgers]

Good Suggestions - I would also like to figure out my Governance Dashboard and troubleshoot my Postgres connection issues. 

In the meantime I ended figuring out the correct syntax for the filters.  It was just my first time working with an API and making SQL queries like this. Was hoping someone had already done the hard work!

RestConnectionMasterTable:
SQL SELECT

"__KEY_data",

(SELECT

    "id" ,
	"name",
    "description",
    "enabled",
    "appId",
    "connectionId",
    "__KEY_items",
    "__FK_items",
    
    (SELECT 
    	"overrideValues",
        "name" as "FieldName",
    	"selectExcluded",
        "__KEY_fields",
		"__FK_fields",
        
        (SELECT 
        	"type",
            "value",
            "__FK_values"
        FROM "values" FK "__FK_values")
       
       
    FROM "fields" PK "__KEY_fields" FK "__FK_fields")
    

FROM "items" PK "__KEY_items" FK "__FK_items")

FROM JSON (wrap off) "data" PK "__KEY_data"
WITH CONNECTION( URL "https://qlik-nprint.killamreit.cloud:4993/api/v1/filters", HTTPHEADER "cookie" "$(vCookie)",QUERY "Limit" "10000") );


[Values]:
LOAD 
type as FilterType,
value as FilterValue,
[__FK_values] as Value_Field_Link
RESIDENT RestConnectionMasterTable
WHERE NOT IsNull([__FK_values]);

[Fields]:
LOAD 
overrideValues as FilterOverrideValues,
"FieldName" as FilterDimenson,
"selectExcluded" as FilterSelectExcluded,
__KEY_fields as Value_Field_Link,
__FK_fields as Field_Item_Link
RESIDENT RestConnectionMasterTable
WHERE NOT IsNull([__FK_fields]);

[NPrint_Filters]:
Load
    "id" as FilterID,
	"name" as FilterName,
    "description" as FilterDescription,
    "enabled" as FilterEnabled,
    "appId",
    "connectionId",
    "__KEY_items" as Field_Item_Link
RESIDENT RestConnectionMasterTable
WHERE NOT IsNull([__FK_items]);


left join (NPrint_Filters)
LOAD 
Field_Item_Link,
FilterDimenson,
FilterSelectExcluded,
FilterOverrideValues,
Value_Field_Link
Resident Fields;

left join (NPrint_Filters)
LOAD
Value_Field_Link,
FilterType,
FilterValue
Resident Values;


DROP TABLE Fields,Values,RestConnectionMasterTable;



//concatenate all filter values for each Dimension
TEXT_STEP1:
NoConcatenate LOAD Distinct
Value_Field_Link,
Field_Item_Link,
if(LastValue(FilterType)='text',
'['&LastValue(FilterDimenson)&']={'&concat(distinct FilterValue,',')&'}'&if(LastValue(FilterSelectExcluded)='True','<excluded>')
)
as FilterValues_Text1,
if(LastValue(FilterType)='number',
'['&LastValue(FilterDimenson)&']={'&concat(distinct FilterValue,',')&'}'&if(LastValue(FilterSelectExcluded)='True','<excluded>')
)
as FilterValues_Number1

Resident NPrint_Filters
group by Value_Field_Link,Field_Item_Link;

[Lech_Miszkiewicz · Accepted solution]

My NPrinting.qvs has it: https://nprintingadventures.com/2019/04/08/nprinting-api-qlik-rest-subroutines/

//  Get Filters

                RestConnectionFiltersTable:
                SQL SELECT
                    "__KEY_data",
                    (SELECT
                        "id",
                        "name",
                        "appId",
                        "__PK_items",
                        "__FK_items",
                        (SELECT
                            "overrideValues",
                            "connectionId",
                            "name" as "Fieldname", 
                            "__PK_fields",
                            "__FK_fields"
                            ,(SELECT
                                "type",
                                "value",
                                "__PK_values",
                                "__FK_values"
                        FROM 
                            "values" PK "__PK_values" FK "__FK_values")
                        FROM 
                            "fields" PK "__PK_fields" FK "__FK_fields")
                    FROM 
                        "items" PK "__PK_items" FK "__FK_items")
                FROM 
                    JSON (wrap off) "data" PK "__KEY_data"
                WITH 
                    CONNECTION( URL "$(vURL_Filters)", HTTPHEADER "cookie" "$(vCookie)", QUERY "Limit" "$(vQueryLimit)")
                ;

[tm_burgers]

Thanks Lech, I found your post and scripts too late after I had already gotten into it.
I have found them now, and we actually had some post dialogue on your site as well.

Thank you for your support!

[Lech_Miszkiewicz]

all good - cheers