Qlik Community

Ask a Question

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
tm_burgers
Creator III
Creator III

Using NPrinting API in QlikSense - GET Filters

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;

 

 

 

 

Labels (2)
1 Solution

Accepted Solutions
Lech_Miszkiewicz

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

 

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

7 Replies
Ruggero_Piccoli
Employee
Employee

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



Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.
tm_burgers
Creator III
Creator III
Author

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
Employee
Employee

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



Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.
tm_burgers
Creator III
Creator III
Author

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

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

 

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

tm_burgers
Creator III
Creator III
Author

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

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.