Qlik NPrinting APIs inside a QlikView load script does not bring all Publish task ID's

[Natalija]

Hi experts,

I was able to connect my Qlikview to NP API to automatically trigger reload of metadata and then report publishing.

My issue is that not all Publish Task Id's are coming back.

If i follow instructions from "How To Use NPrinting APIs In A Qlik Load Script " and  select only one application for testing purposes (in WHERE clause) it works as it should, but when i want to bring all my NP tasks, some of them are empty (example attached). Not sure what to do, as inside webconsole i have those publish task ID's, but it doesn't come from source.

Did anyone had same issue? Is there a limit how many tasks you can import in one go? At the moment i have more or less 51 publish tasks.

[Lech_Miszkiewicz]

Hi,

this is actually well documented in official documentation and on community. Default response gets first 50 records. To override this you need to add LIMIT parameter to your connection. See below example where there are 300 users brought in:

FROM "items" FK "__FK_items")
FROM JSON (wrap off) "data" PK "__KEY_data"
WITH CONNECTION(
URL "https://nprintingserver:4993/api/v1/users?limit=300",
HTTPHEADER "cookie" "$(vCookie)"
);
// You need to set the limit to the number of users in your NPrinting server for this query to return proper results, by default only the first 50 users are returned by the API and they might not be the one in your group

[Lech_Miszkiewicz]

I also encourage you to review this:

https://nprintingadventures.wordpress.com/2019/04/08/nprinting-api-qlik-rest-subroutines/

Subroutines don't have limit parameter embeded but this can be easily fixed...

[Natalija]

Thank you Lech,

Not sure how did I miss that 😄

Many thanks,

Natalija

[Natalija]

Hi Lech,

just a quick additional question to this discussion. I have reports that are build from 10 NP connections. When I am running following script to automatically reload tasks in WebConsole, it only finds one connection to reload metadata and then distributes report. Issue is that I need to reload all 10 of them to send out the report.

Am I missing something? Could you please direct me to any articles/existing blogs regarding same/similar issue?

Many thanks,

Natalija

[Lech_Miszkiewicz]

Hi,

Are you using my subroutines or have you built your own script?

If you are using my subroutines to answer your question I would need to see your script as I have no idea how you are calling them?

In my subroutines i have this piece of code:

//	    GET CONNECTIONS         - This procedure returns Connection Id's based on their name(s)     (Parameters: Connection Name, AppId)

            SUB NP_GetConnections(vNPrintingConnectionName)

                //  Get Connection

                    LIB CONNECT TO '$(vConnection_GET)';

                //  GET ONE, FEW or ALL Connections
                    
                    Let vNPrintingConnectionOneOrAll        =   If('$(vNPrintingConnectionName)'='','ALL','$(vNPrintingConnectionName)');
                    
                    If '$(vNPrintingConnectionOneOrAll)'    =   'ALL'
                        Let vNPrintingConnectionName        =   '*';
                    Else
                        ConnectionListTbl:
                        Load
                            SubField('$(vNPrintingConnectionName)',',') as ConnectionListTbl
                        Autogenerate
                            (1)
                        ;    
                    EndIf

                    Trace Running "$(vNPrintingConnectionOneOrAll)" Connection(s);

The above code depending on your inputs will reload all connection, or list of connections or one (if you only put name of one connection in it)....

If you have written your own code i guess it is up to you how you want to solve the metagata generation step...

[Natalija]

Hi Lech,

Sorry for late reply. This is code that i am using in my generator.

let vAPIConnectionNameForGET = "My Newwstand"

let vAPIConnectionNameForPOST = "My Newsstand"

	CUSTOM CONNECT TO $(vAPIConnectionNameForGET);

	CUSTOM CONNECT TO $(vAPIConnectionNameForPOST);

CUSTOM CONNECT TO $(vAPIConnectionNameForGET);

RestConnectorMasterTable: 
SQL SELECT   "Set-Cookie",  "__KEY__response_header" FROM JSON "_response_header" PK "__KEY__response_header"; 
 
[_response_header]: 
LOAD [Set-Cookie] AS [Set-Cookie]

RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__KEY__response_header]); 
 
//Extracts session cookie from the API response 
let vCookieRaw = Peek('Set-Cookie',0,'_response_header');  
let vCookie = TextBetween('$(vCookieRaw)','Secure,','Path=/',2); 
 
DROP TABLE RestConnectorMasterTable;  


//NP GET CONNECTION extracts NP Connection ID's

RestConnectionMasterTable:
SQL SELECT  

"__KEY_data",
(SELECT "id","name","appId","__FK_items"
FROM "items" FK "__FK_items")

FROM JSON (wrap off) "data" PK "__KEY_data"

WITH CONNECTION( URL "My Newsstand:4993/api/v1/connections", HTTPHEADER "cookie" "$(vCookie)",QUERY "Limit" "1000000" );

[connection_items]:

LOAD

[id] 	AS [connection_id],
[name]  AS [connection_name],
[appId] AS [connection_appId],
[appId] as %Connection_ID_Key

RESIDENT RestConnectionMasterTable WHERE NOT IsNull([__FK_items]) ;

let vConnectionId = Peek('connection_id',0,'connection_items');

let vReloadMetadataURL = 'My Newsstand:4993/api/v1/connections/'&'$(vConnectionId)'&'/reload';//Compose the URL for the GET call that checks the connection status

let vConnectionStatusURL = 'My Newsstand:4993/api/v1/connections/'&'$(vConnectionId)';

DROP TABLE RestConnectionMasterTable;


//Triggers NP to reload metadata

CUSTOM CONNECT TO $(vAPIConnectionNameForPOST);

RestNPReloadMetadataTable:
SQL SELECT
"__KEY_data"

FROM JSON (wrap off) "data" PK "__KEY_data"
WITH CONNECTION( URL "$(vReloadMetadataURL)", HTTPHEADER "cookie" "$(vCookie)",QUERY "Limit" "1000000");

[metadata_items]:
LOAD 
[__KEY_data] AS [__KEY_data]

RESIDENT RestNPReloadMetadataTable WHERE NOT IsNull([__KEY_data]);


DROP TABLE RestNPReloadMetadataTable;


//NP METADATA STATUS

CUSTOM CONNECT TO $(vAPIConnectionNameForGET);

let vConnectionStatus = '';

DO while (vConnectionStatus <> 'Generated')   

RestConnectionStatusTable:  
SQL SELECT    
"cacheStatus"  FROM JSON (wrap off) "data"

WITH CONNECTION( URL "$(vConnectionStatusURL)", HTTPHEADER "cookie" "$(vCookie)",QUERY "Limit" "1000000");

[connection_data]:  

LOAD
[cacheStatus] AS [connection_cacheStatus]

RESIDENT RestConnectionStatusTable;  

let vConnectionStatus = Peek('connection_cacheStatus',0,'connection_data');  

DROP TABLE RestConnectionStatusTable;  
DROP TABLE [connection_data];
Loop 

//NP GET TASKS = all available Publish Tasks in NP

RestNPTasksMasterTable: 
SQL SELECT  
"__KEY_data",
(SELECT"id","name","appId","__FK_items"

FROM "items" FK "__FK_items")
FROM JSON (wrap off) "data" PK "__KEY_data"
WITH CONNECTION( URL "My Newsstand:4993/api/v1/tasks", HTTPHEADER "cookie" "$(vCookie)",QUERY "Limit" "1000000" );

[task_items]:
LOAD
[id]    AS [tasks_taskId],
[name]  AS [tasks_taskName],
[appId] AS [tasks_appId],
[appId] AS %Task_ID_Key,
[name]  AS %Task_ID_Name

RESIDENT RestNPTasksMasterTable WHERE NOT IsNull([__FK_items]);// AND [appId] = '$(vAppId)';

let vTaskId = Peek('tasks_taskId',0,'task_items'); 

let vPublshTaskURL = 'My Newsstand:4993/api/v1/tasks/'&'$(vTaskId)'&'/executions';

DROP TABLE RestNPTasksMasterTable;



//Triggers NP to publish reports

CUSTOM CONNECT TO $(vAPIConnectionNameForPOST);

RestNPTaskTriggerTable:
SQL SELECT
"__KEY_data"
FROM JSON (wrap off) "data" PK "__KEY_data"

WITH CONNECTION( URL "$(vPublshTaskURL)", HTTPHEADER "cookie" "$(vCookie)",QUERY "Limit" "1000000");

[_post_items]:
LOAD

[__KEY_data] AS [__KEY_data]
 
RESIDENT RestNPTaskTriggerTable WHERE NOT IsNull([__KEY_data]);
 
DROP TABLE RestNPTaskTriggerTable;

[Lech_Miszkiewicz]

Hi @Natalija 

Well in your script you are loading all connections to connection_items table but you are only picking first connection as per this script (which i have found in your logic)

let vConnectionId = Peek('connection_id',0,'connection_items');

In my scripts I loop through all rows in the connection_items  table. My table connection_items table is created like this:

                    connection_items:
                    LOAD 
                        RowNo()         as connection_row_no,
                        id 			    as connection_id,
                        name 			as connection_name,
                        appId 		    as connection_appId
                    RESIDENT 
                        RestConnectionMasterTable
                    WHERE 
                        NOT IsNull(__FK_items) 
                        AND appId = '$(vAppId)'
                        and (Exists(ConnectionListTbl,name) or name like '$(vNPrintingConnectionName)')
                    ;

In NPrinting.qvs search for subroutines:

• SUB NP_GetConnections(vNPrintingConnectionName)
• SUB NP_ConnectionReload(vNPrintingConnectionName,vNP_ReloadDuration)

you will see that  NP_ConnectionReload subroutine has this bit where all i go over each line based  on connection_row_no field...."FOR Each vRowNo in FieldValueList('connection_row_no')..... "

(see this part - rest is in NPrinting .qvs I have pointetd you to above in my previous post)

            SUB NP_ConnectionReload(vNPrintingConnectionName,vNP_ReloadDuration)

                //  Get Connections
                Call NP_GetConnections('$(vNPrintingConnectionName)')

                    FOR Each  vRowNo in FieldValueList('connection_row_no');  

                        //	Extracts the desired Connection ID
                
                        Let vConnectionId               = Peek('connection_id',$(vRowNo)-1,'connection_items');
                        Let vNPrintingConnectionName    = Peek('connection_name',$(vRowNo)-1,'connection_items');
                        //	Trace            
                        Trace 	---;
                        Trace 	Derived ConnectionId for Connection $(vNPrintingConnectionName) is $(vConnectionId);

                        //  Compose the URL for the POST call that triggers a reload metadata

                        Let vReloadMetadataURL      = 'https://$(vNPrintingServer):4993/api/v1/connections/$(vConnectionId)/reload';
                        Trace Reload Metadata URL: ;
                        Trace "$(vReloadMetadataURL)";

                        //  Compose the URL for the GET call that checks the connection status

                        Let vConnectionStatusURL    = 'https://$(vNPrintingServer):4993/api/v1/connections/$(vConnectionId)';
                        Trace Connection Status URL: ;
                        Trace "$(vConnectionStatusURL)" ;

cheers