Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Active Directory Query

Hi Guys,

I am pulling the user attributes from AD using the below script. And it is working great. But I have another LDAP source as eu.corp

How to include this in the same script?

OLEDB CONNECT TO [Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Data Source=LDAP://na.corp;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];

LET arg=chr(01);

DO

[NA Users]:

SQL select name,

givenName,

sn,

sAMAccountName,

physicalDeliveryOfficeName,

l,

st,

co,

c

FROM 'LDAP://na.corp' WHERE objectCategory='person' AND objectClass = 'User' and name >'$(arg)' order by name;

EXIT DO WHEN ScriptError > 1;

EXIT DO WHEN NoOfRows('nameTable') = 0;

EXIT DO WHEN peek('name') = '$(arg)';

LET arg=peek('name');

1 Solution

Accepted Solutions
quwok
Creator III
Creator III

You can use LOAD to rename the column, i.e.

DO

[EU Users]:

LOAD

name as eu_name,

givenName as eu_givenName,

sn as eu_sn,

sAMAccountName as eu_sAMAccountName,

physicalDeliveryOfficeName as eu_physicalDeliveryOfficeName,

l as eu_l,

st as eu_st,

co as eu_co,

c as eu_c

;

SQL select name,

givenName,

sn,

sAMAccountName,

physicalDeliveryOfficeName,

l,

st,

co,

c

FROM 'LDAP://eu.corp' WHERE objectCategory='person' AND objectClass = 'User' and name >'$(arg)' order by name;

You can see in the above script that we have renamed the columns with a preceding "eu_", you can name them to anything you like. There are other ways such as using QUALIFY, but i think that might be a topic once you are more comfortable with QlikView scripting.

View solution in original post

7 Replies
quwok
Creator III
Creator III

QlikView can only connect to one data source at a time, you'll need to do something like:

CONNECT source1

Load all tables required from source1

CONNECT source2

Load all tables required from source2

script to relate source1 and source2 tables


Anonymous
Not applicable
Author

So I added 2 scripts like below. One for NA and One for EU.

But after reload. I am not able to see the 2nd table for EU in the table viewer???

OLEDB CONNECT TO [Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Data Source=LDAP://na.corp;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];

LET arg=chr(01);

DO

[NA Users]:

SQL select name,

givenName,

sn,

sAMAccountName,

physicalDeliveryOfficeName,

l,

st,

co,

c

FROM 'LDAP://na.corp' WHERE objectCategory='person' AND objectClass = 'User' and name >'$(arg)' order by name;

EXIT DO WHEN ScriptError > 1;

EXIT DO WHEN NoOfRows('nameTable') = 0;

EXIT DO WHEN peek('name') = '$(arg)';

LET arg=peek('name');

OLEDB CONNECT TO [Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Data Source=LDAP://eu.corp;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];

LET arg=chr(01);

DO

[EU Users]:

SQL select name,

givenName,

sn,

sAMAccountName,

physicalDeliveryOfficeName,

l,

st,

co,

c

FROM 'LDAP://eu.corp' WHERE objectCategory='person' AND objectClass = 'User' and name >'$(arg)' order by name;

EXIT DO WHEN ScriptError > 1;

EXIT DO WHEN NoOfRows('nameTable') = 0;

EXIT DO WHEN peek('name') = '$(arg)';

LET arg=peek('name');

quwok
Creator III
Creator III

Looking at your script, both tables have the same structure, QlikView might have concatenated both into a single table. If you like to load each source into their own table, try changing the column names on the second source.

Anonymous
Not applicable
Author

Thanks for the reply. Need your help here

I tried renaming the SN in the 2nd table as SN as 'ABCD' but I am getting error.

Can you please in renaming the column name in the 2nd table.

quwok
Creator III
Creator III

You can use LOAD to rename the column, i.e.

DO

[EU Users]:

LOAD

name as eu_name,

givenName as eu_givenName,

sn as eu_sn,

sAMAccountName as eu_sAMAccountName,

physicalDeliveryOfficeName as eu_physicalDeliveryOfficeName,

l as eu_l,

st as eu_st,

co as eu_co,

c as eu_c

;

SQL select name,

givenName,

sn,

sAMAccountName,

physicalDeliveryOfficeName,

l,

st,

co,

c

FROM 'LDAP://eu.corp' WHERE objectCategory='person' AND objectClass = 'User' and name >'$(arg)' order by name;

You can see in the above script that we have renamed the columns with a preceding "eu_", you can name them to anything you like. There are other ways such as using QUALIFY, but i think that might be a topic once you are more comfortable with QlikView scripting.

Anonymous
Not applicable
Author

Thanks Kwok. Your idea is working very well.

Anonymous
Not applicable
Author

Hi,


Do you need a special permission to query LDAP?  When I tested the connection it gave me these messages.

"Test connection failed because of an error in setting the window handle property. Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Continue with test connection?"

I clicked Yes and the second message came up.

"Test connection succeeded but some setting were not accepted by the provider."


It generated the connection string.

I issued the statement below and reloaded.  I got a script error showing the statement below in the pop up.

SQL

select name

FROM 'ldap://dc-1.local';

Do I need to do anything different?

Thanks,

Stewart