Qlik Community

Ask a Question

Qlik Sense Deployment & Management

Discussion board where members learn more about Qlik Sense Installation, Deployment and Management.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mickael_weqan
Partner
Partner

SQL UDC based on views

Hello community,

is it possible to configure an user directory connector based on 2 views?

I've got this issue when I apply my configuration :  Error when validating tables: One or more of the expected tables QS_USER and QS_USER_ATTRIBUTE are not present.

QS_USER and QS_USER_ATTRIBUTE are 2 views with this schema :

Capture.PNG

QS_USER_ATTRIBUTE is empty for the moment.

Thank you for your answer,

Mickael.

1 Solution

Accepted Solutions
Levi_Turner
Employee
Employee

Sure.

Sample code:

-- Create the database if needed
CREATE DATABASE QlikUsers;

-- Create table basic user table
USE [QlikUsers]
GO

CREATE TABLE users_basic (
userid varchar(255) NOT NULL,
name varchar(255)
);

-- Fill in user info
USE [QlikUsers]
GO

INSERT INTO [dbo].[users_basic]
([userid]
,[name])
VALUES
('jhdoe', 'John Doe');

-- Create table basic attributes table
USE [QlikUsers]
GO

CREATE TABLE attributes_basic (
userid varchar(255) NOT NULL,
type varchar(255),
value varchar(255),
);

-- Fill in attribute info
USE [QlikUsers]
GO

INSERT INTO [dbo].[attributes_basic]
([userid]
,[type]
,[value])
VALUES
('jhdoe', 'Role', 'foo');

-- Create user view
CREATE VIEW [user_basic_view] AS
SELECT userid, name
FROM [dbo].[users_basic];

-- Create attribute view

CREATE VIEW [attributes_basic_view] AS
SELECT userid, type, value
FROM [dbo].[attributes_basic];

Sense Configuration:

kmgh5u6.png

View solution in original post

2 Replies
Levi_Turner
Employee
Employee

Sure.

Sample code:

-- Create the database if needed
CREATE DATABASE QlikUsers;

-- Create table basic user table
USE [QlikUsers]
GO

CREATE TABLE users_basic (
userid varchar(255) NOT NULL,
name varchar(255)
);

-- Fill in user info
USE [QlikUsers]
GO

INSERT INTO [dbo].[users_basic]
([userid]
,[name])
VALUES
('jhdoe', 'John Doe');

-- Create table basic attributes table
USE [QlikUsers]
GO

CREATE TABLE attributes_basic (
userid varchar(255) NOT NULL,
type varchar(255),
value varchar(255),
);

-- Fill in attribute info
USE [QlikUsers]
GO

INSERT INTO [dbo].[attributes_basic]
([userid]
,[type]
,[value])
VALUES
('jhdoe', 'Role', 'foo');

-- Create user view
CREATE VIEW [user_basic_view] AS
SELECT userid, name
FROM [dbo].[users_basic];

-- Create attribute view

CREATE VIEW [attributes_basic_view] AS
SELECT userid, type, value
FROM [dbo].[attributes_basic];

Sense Configuration:

kmgh5u6.png

View solution in original post

mickael_weqan
Partner
Partner

Hi Levi,

thanks for your answer,

issue was the attribute table, defined with varchar(0).

UDC is working well now.