topic Re: Using NPrinting API in QlikSense - GET Filters in Qlik NPrinting

Using NPrinting API in QlikSense - GET Filters

tm_burgers — Mon, 25 Jan 2021 15:43:38 GMT

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;

Re: Using NPrinting API in QlikSense - GET Filters

Ruggero_Piccoli — Mon, 25 Jan 2021 16:01:08 GMT

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

Re: Using NPrinting API in QlikSense - GET Filters

tm_burgers — Mon, 25 Jan 2021 16:32:19 GMT

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.

Re: Using NPrinting API in QlikSense - GET Filters

Ruggero_Piccoli — Tue, 26 Jan 2021 08:03:17 GMT

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-trail.htm 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

Re: Using NPrinting API in QlikSense - GET Filters

tm_burgers — Tue, 26 Jan 2021 12:47:00 GMT

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;

Re: Using NPrinting API in QlikSense - GET Filters

Lech_Miszkiewicz — Tue, 26 Jan 2021 22:13:00 GMT

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)") ;

Re: Using NPrinting API in QlikSense - GET Filters

tm_burgers — Wed, 27 Jan 2021 00:03:44 GMT

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!

Re: Using NPrinting API in QlikSense - GET Filters

Lech_Miszkiewicz — Wed, 27 Jan 2021 04:17:09 GMT

all good - cheers