Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jpbartolomeo
New 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
New Contributor III

Re: QlikSense - Connection to Active Directory

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!

8 Replies

Re: QlikSense - Connection to Active Directory

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?

Life is so rich, and we need to respect to the life !!!
jpbartolomeo
New Contributor III

Re: QlikSense - Connection to Active Directory

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?

Re: QlikSense - Connection to Active Directory

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

Life is so rich, and we need to respect to the life !!!
jpbartolomeo
New Contributor III

Re: QlikSense - Connection to Active Directory

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
New Contributor III

Re: QlikSense - Connection to Active Directory

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.

Re: QlikSense - Connection to Active Directory

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

Life is so rich, and we need to respect to the life !!!
jpbartolomeo
New Contributor III

Re: QlikSense - Connection to Active Directory

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
New Contributor III

Re: QlikSense - Connection to Active Directory

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!

Community Browser