Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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
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
Thanks Ryookabe, it works fine 🙂
Hi Ryo_okabe,
I am trying to modify the group name of a particular user using the NP API's. I am using the put user/<id>/groups command to do so.
Data retrieved for Get user/<id>/groups is in the form of an array and m not sure how manipulate this.
But my code is failing in preparing the body request for API. Can you please help ?