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: 
jpbartolomeo
Partner - Contributor III
Partner - Contributor III

QlikSense - Connection to Active Directory

Dear Community:

I have the next code running ok in Qlikview. But the same code doesn't run in Qliksense, even with the connector ok I've already checked the thread Connecting to and Querying Active Directory for Users:

LET arg=chr(01);

DO

ADUsers:

First 500

LOAD

name,

sAMAccountName as [8ID],

cn as NombreCompleto,

DistinguishedName as KEY,

If(IsNull(physicaldeliveryofficename), '*-*', physicaldeliveryofficename) as Oficina,

If(Len(Trim(department)) = 0, '*-*', department) as Departamento,

If(IsNull(title), '*-*', title ) as Puesto,

ipphone as TelTrabajo,

If(IsNull(mail), '*-*', mail) as Email,

company as Compañia,

If(IsNull(manager), '*-*', Mid(manager, 4, Index(manager, ',') - 4)) as Supervisor,

If(IsNull(genero), '*-*', genero) as Genero,

If(IsNull(tcontrato), '*-*', tcontrato) as TipoContrato,

If(IsNull(estado), '*-*', estado) as Estado,

tsalida as TipoSalida,

Date#(fingreso, 'DDMMYYYY') as FechaIngreso,

Date#(fsalida, 'DDMMYYYY') as FechaSalida;

      

SQL SELECT

name,

sAMAccountName,

DistinguishedName,

cn,

physicaldeliveryofficename,

department,

title,

ipphone,

mail,

company,

manager,

genero,

tcontrato,

estado,

tsalida,

fingreso,

fsalida

FROM 'LDAP://192.168.0.1'    

WHERE objectCategory = 'user'

  and name > '$(arg)' order by name;

EXIT DO WHEN ScriptError > 1;

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

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

LET arg = peek('name');

LOOP;

The error is "QVX_UNEXPECTED_END_OF_DATA".

Thanks in advance.



1 Solution

Accepted Solutions
jpbartolomeo
Partner - Contributor III
Partner - Contributor III
Author

So, this is my workaround:

LIB CONNECT TO 'LDAP-- (user)';

//Users begining with 0-9

For i=48 to 57 //Unicode chr(48)=0, chr(57)=9

  LET char = chr($(i));

 

  AD:

  LOAD

     name,

     sAMAccountName as [8ID],

     cn as NombreCompleto,

     DistinguishedName as KEY,

     If(IsNull(physicaldeliveryofficename), '*-*', physicaldeliveryofficename) as Oficina,

     If(Len(Trim(department)) = 0, '*-*', department) as Departamento,

     If(IsNull(title), '*-*', title ) as Puesto,

     ipphone as TelTrabajo,

     If(IsNull(mail), '*-*', mail) as Email,

     company as Compañia,

     If(IsNull(manager), '*-*', Mid(manager, 4, Index(manager, ',') - 4)) as Supervisor,

     If(IsNull(genero), '*-*', genero) as Genero,

     If(IsNull(tcontrato), '*-*', tcontrato) as TipoContrato,

     If(IsNull(estado), '*-*', estado) as Estado,

     tsalida as TipoSalida,

     Date#(fingreso, 'DDMMYYYY') as FechaIngreso,

     Date#(fsalida, 'DDMMYYYY') as FechaSalida;

  SQL select

       name,

       sAMAccountName,

       DistinguishedName,

       cn,

       physicaldeliveryofficename,

       department,

       title,

       ipphone,

       mail,

       company,

       manager,

       genero,

       tcontrato,

       estado,

       tsalida,

       fingreso,

       fsalida

  FROM 'LDAP://192.168.0.1'

  WHERE objectCategory='person' and name='$(char)*'   ;

Next i;

// Users begining with A-Z

For i=65 to 90

  LET char = chr($(i)); //Unicode chr(65)= a, chr(90)= z (AD is not case sensitive)

 

AD:

LOAD

     name,

     sAMAccountName as [8ID],

     cn as NombreCompleto,

     DistinguishedName as KEY,

     If(IsNull(physicaldeliveryofficename), '*-*', physicaldeliveryofficename) as Oficina,

     If(Len(Trim(department)) = 0, '*-*', department) as Departamento,

     If(IsNull(title), '*-*', title ) as Puesto,

     ipphone as TelTrabajo,

     If(IsNull(mail), '*-*', mail) as Email,

     company as Compañia,

     If(IsNull(manager), '*-*', Mid(manager, 4, Index(manager, ',') - 4)) as Supervisor,

     If(IsNull(genero), '*-*', genero) as Genero,

     If(IsNull(tcontrato), '*-*', tcontrato) as TipoContrato,

     If(IsNull(estado), '*-*', estado) as Estado,

     tsalida as TipoSalida,

     Date#(fingreso, 'DDMMYYYY') as FechaIngreso,

     Date#(fsalida, 'DDMMYYYY') as FechaSalida;

  SQL select

       name,

       sAMAccountName,

       DistinguishedName,

       cn,

       physicaldeliveryofficename,

       department,

       title,

       ipphone,

       mail,

       company,

       manager,

       genero,

       tcontrato,

       estado,

       tsalida,

       fingreso,

       fsalida

  FROM 'LDAP://192.168.0.1'

  WHERE objectCategory='person' and name='$(char)*'   ;

Next i;

I hope this will be useful. Thanks Anil for your help!

View solution in original post

8 Replies
Anil_Babu_Samineni

I believe you've connected thru ODBC, And my suggestion is OLEDB to resolve this issue

And one more thought, I have do you have any non english fields data with you?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jpbartolomeo
Partner - Contributor III
Partner - Contributor III
Author

Anil:

Many thanks for the response.

I'm using OLE DB to connect but, like you said, I have in the data non-english fields (my language is Spanish).

Do you know how to retrieve them?

Anil_Babu_Samineni

That means, you have English and Non English data with you? or only non English data if so we need to change our extension of En-US to required one

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jpbartolomeo
Partner - Contributor III
Partner - Contributor III
Author

Anil:

Basicaly we have non-english data. But I suppose it doen't affect having english data too. With non-english data we have special characters, plus all the characters of the English language.

What do you mean with changing the extension to requiered one? The only place I know to configure the language is with the variable CollationLocale in the Main tab, which currently is 'es-CL' .


Thanks!

jpbartolomeo
Partner - Contributor III
Partner - Contributor III
Author

Anil:

If I query this, it works fine:

SQL select *

FROM 'LDAP://10.148.1.6'

WHERE objectCategory='person' and name='a*'   ;

But obviously, I'm quering the user starting with "a".

Hope it helps to find the answer.

Anil_Babu_Samineni

This will work, May be ObjectCategory and name carrying the person and a values. Do you want this in Qlik?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jpbartolomeo
Partner - Contributor III
Partner - Contributor III
Author

Anil:

I want all the users.

We found a workaround for this issue, looping through all the first letter's alphabet characters  and numbers.

I'll post the result when I finish.

Thanks!

jpbartolomeo
Partner - Contributor III
Partner - Contributor III
Author

So, this is my workaround:

LIB CONNECT TO 'LDAP-- (user)';

//Users begining with 0-9

For i=48 to 57 //Unicode chr(48)=0, chr(57)=9

  LET char = chr($(i));

 

  AD:

  LOAD

     name,

     sAMAccountName as [8ID],

     cn as NombreCompleto,

     DistinguishedName as KEY,

     If(IsNull(physicaldeliveryofficename), '*-*', physicaldeliveryofficename) as Oficina,

     If(Len(Trim(department)) = 0, '*-*', department) as Departamento,

     If(IsNull(title), '*-*', title ) as Puesto,

     ipphone as TelTrabajo,

     If(IsNull(mail), '*-*', mail) as Email,

     company as Compañia,

     If(IsNull(manager), '*-*', Mid(manager, 4, Index(manager, ',') - 4)) as Supervisor,

     If(IsNull(genero), '*-*', genero) as Genero,

     If(IsNull(tcontrato), '*-*', tcontrato) as TipoContrato,

     If(IsNull(estado), '*-*', estado) as Estado,

     tsalida as TipoSalida,

     Date#(fingreso, 'DDMMYYYY') as FechaIngreso,

     Date#(fsalida, 'DDMMYYYY') as FechaSalida;

  SQL select

       name,

       sAMAccountName,

       DistinguishedName,

       cn,

       physicaldeliveryofficename,

       department,

       title,

       ipphone,

       mail,

       company,

       manager,

       genero,

       tcontrato,

       estado,

       tsalida,

       fingreso,

       fsalida

  FROM 'LDAP://192.168.0.1'

  WHERE objectCategory='person' and name='$(char)*'   ;

Next i;

// Users begining with A-Z

For i=65 to 90

  LET char = chr($(i)); //Unicode chr(65)= a, chr(90)= z (AD is not case sensitive)

 

AD:

LOAD

     name,

     sAMAccountName as [8ID],

     cn as NombreCompleto,

     DistinguishedName as KEY,

     If(IsNull(physicaldeliveryofficename), '*-*', physicaldeliveryofficename) as Oficina,

     If(Len(Trim(department)) = 0, '*-*', department) as Departamento,

     If(IsNull(title), '*-*', title ) as Puesto,

     ipphone as TelTrabajo,

     If(IsNull(mail), '*-*', mail) as Email,

     company as Compañia,

     If(IsNull(manager), '*-*', Mid(manager, 4, Index(manager, ',') - 4)) as Supervisor,

     If(IsNull(genero), '*-*', genero) as Genero,

     If(IsNull(tcontrato), '*-*', tcontrato) as TipoContrato,

     If(IsNull(estado), '*-*', estado) as Estado,

     tsalida as TipoSalida,

     Date#(fingreso, 'DDMMYYYY') as FechaIngreso,

     Date#(fsalida, 'DDMMYYYY') as FechaSalida;

  SQL select

       name,

       sAMAccountName,

       DistinguishedName,

       cn,

       physicaldeliveryofficename,

       department,

       title,

       ipphone,

       mail,

       company,

       manager,

       genero,

       tcontrato,

       estado,

       tsalida,

       fingreso,

       fsalida

  FROM 'LDAP://192.168.0.1'

  WHERE objectCategory='person' and name='$(char)*'   ;

Next i;

I hope this will be useful. Thanks Anil for your help!