8 Replies Latest reply: Jan 2, 2018 12:30 PM by Juan Pablo Bartolomeo RSS

    QlikSense - Connection to Active Directory

    Juan Pablo Bartolomeo

      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.



        • Re: QlikSense - Connection to Active Directory
          Anil Babu

          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?

          • Re: QlikSense - Connection to Active Directory
            Juan Pablo Bartolomeo

            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!