Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!
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?
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?
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
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!
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.
This will work, May be ObjectCategory and name carrying the person and a values. Do you want this in Qlik?
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!
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!