Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

asharppresidio
New Contributor

Convert GUID to correct format

Hi

I am very new to QlikView and have been searching for a way to convert a GUID. In sql I have to convert it to a uniqueidentifier to get the correct value but QlikView will not allow me to add that to the LOAD sql statement.  The below is a portion of my code. I have also tried CAST and that also throws and error.

What I get: CB1BFE06ADC74444B9729AA5FCF35096

What I need to get (and works in SQL): 06FE1BCB-C7AD-4444-B972-9AA5FCF35096

Please let me know of any suggestions! Thanks!

Amy

LOAD DISTINCT
  name as UserName
,
displayName
,
distinguishedName as UserDN
,
objectcategory
,
objectGUID
,
userAccountControl
,
extensionattribute15
,
title
,
lastLogon
;
SQL
select
name
, displayName, distinguishedName, objectcategory, objectGUID, userAccountControl,extensionattribute15, title
, lastLogon
, CONVERT(UNIQUEIDENTIFIER, ObjectGUId) as AD_GUID
FROM 'LDAP://$(RootDse)'
where name>'$(arg)'

and objectCategory = 'Person'  ;

Tags (2)
3 Replies
atoz1158
Contributor

Re: Convert GUID to correct format

In your load bit after the line

,lastLogon

you need to add

,AD_GUID

asharppresidio
New Contributor

Re: Convert GUID to correct format

Thanks! But I have tried that and it throws an error. I left that line it to show what I was trying to pull in.

Re: Convert GUID to correct format

Hi,

maybe you could use a preceding load and convert the ObjectGUId like this:

Left(ObjectGUId,8)&'-'&Mid(ObjectGUId,9,4)&'-'&Mid(ObjectGUId,13,4)&'-'&Mid(ObjectGUId,17,4)&'-'&Mid(ObjectGUId,21) as ObjectGUId

hope this helps

regards

Marco

Community Browser