Qlik Community

Qlik Sense Deployment & Management

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

mickael_weqan
New Contributor III

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

Re: SQL UDC based on views

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

2 Replies
Employee
Employee

Re: SQL UDC based on views

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

mickael_weqan
New Contributor III

Re: SQL UDC based on views

Hi Levi,

thanks for your answer,

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

UDC is working well now.

Community Browser