Qlik Community

Qlik Connectors Discussions

Discussion Board for collaboration regarding Qlik Connectors.

Highlighted
JeromeS
New Contributor

Nprinting Rest Get user groups

Dear qlik developers,

I am trying to load a table containing all nprinting users and the groups they belong to using a script in qlik sense and a rest connection.

I have managed to load a table containing all the users basic information but I can't figure out how to select the groups attached to each user

Any help or tip would be greatly appreciated


Here is an exemple of the json structure that i cannot address:

 

 nprinting_get_groups.jpg

 

Here is the script to load users details (using Qlik Nprinting API)

 

RestUserMasterTable:

SQL SELECT

"__KEY_data",

(SELECT

"id",
"email",
"created",
"lastUpdate", 
"enabled",
"userName",
"domainAccount",
"timezone",
"locale",
"folder",
"subFolder",
"__FK_items"

FROM "items" FK "__FK_items")

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

WITH CONNECTION( URL "https://<nprintingurl>/api/v1/users", HTTPHEADER "cookie" "$(vCookie)" );

users_items:

LOAD 
[id] AS users_id,
[email] AS users_email,
[created] AS users_created,
[lastUpdate] AS users_lastUpdate, 
[enabled] AS users_enabled,
[userName] AS users_userName,
[domainAccount] AS users_domainAccount,
[timezone] AS users_timezone,
[locale] AS users_locale,
[folder] AS users_folder,
[subFolder] AS users_subFolder

RESIDENT RestUserMasterTable;

DROP TABLE RestUserMasterTable;



 

 

Here is my draft script to load users groups .

The script executes itself without error but no data is loaded for field "usergroups_id"

 

 

For i=0 to NoOfRows('users_items')-2

let vUserid= peek('users_id',$(i),'users_items');

let vUrl = 'https://<nprinting url>/api/v1/users/'&  '$(vUserid)'& '/groups' ;

RestUsersGroupsMasterTable:

SQL SELECT 

"__KEY_data",

(SELECT 

"groupids"

FROM "items" FK "__FK_items")

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

WITH CONNECTION( URL "$(vUrl)", HTTPHEADER "cookie" "$(vCookie)" );

Usergroups_items:

LOAD 

'$(vUserid)' as usergroups_user,
[groupids] as usergroups_id

Resident RestUsersGroupsMasterTable;


NEXT i

 

 

Jérôme

 

 

1 Reply
Luminary
Luminary

Re: Nprinting Rest Get user groups

 

Hi,

 

Why don't you try to use following sample code.

It works fine in my environment.

 

Following is get user data.

// Get User with "usera" email
RestUserMasterTable:
SQL SELECT
"__KEY_data",
(SELECT
"id",
"email",
"__FK_items"
FROM "items" FK "__FK_items")
FROM JSON (wrap off) "data" PK "__KEY_data"
WITH CONNECTION( URL "https://<NPServer IP>:4993/api/v1/users", HTTPHEADER "cookie" "$(vCookie)" );

[users_items]:
LOAD [id] AS [users_userId],
[email] AS [users_userEmail]
RESIDENT RestUserMasterTable
WHERE NOT IsNull([__FK_items]);
//Extracts the userId of the desired NP User
let vUserId = Peek('users_userId',0,'users_items');
DROP TABLE RestUserMasterTable;

 

Following is get group data and get user groups.

 

 

RestGroupMasterTable:
SQL SELECT
"__KEY_data",
(SELECT 
"name",
"id",
"__FK_items",
"Created"
FROM "items" FK "__FK_items")
FROM JSON (wrap off) "data" PK "__KEY_data"
WITH CONNECTION( URL "https://<NPServer IP>:4993/api/v1/Groups", HTTPHEADER "cookie" "$(vCookie)" );


[group]:
LOAD [id] AS [groups_groupId],
    [name] AS [groups_groupName]
RESIDENT RestGroupMasterTable
WHERE NOT IsNull([__FK_items]);
let vUserId = Peek('users_userId',0,'users_items');
DROP TABLE RestGroupMasterTable;


For i = 0 to NoOfRows('group')-2

let vGroupid= peek('groups_groupId',$(i),'group');

let vUrl = 'https://<NPServer IP>:4993/api/v1/Groups/'& '$(vGroupid)'& '/Users' ;

RestConnectorMasterTable:
Load @Value as [users_userId],
     '$(vGroupid)' as [groups_groupId];
SQL SELECT 
	"__KEY_data",
	(SELECT 
		"@Value",
		"__FK_items"
	FROM "items" FK "__FK_items" ArrayValueAlias "@Value")
FROM JSON (wrap off) "data" PK "__KEY_data"
WITH CONNECTION( URL "$(vUrl)", HTTPHEADER "cookie" "$(vCookie)" );

next

 

Community Browser