Convert GUID to correct format

[asharppresidio]

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'  ;

[atoz1158]

In your load bit after the line

,lastLogon

you need to add

,AD_GUID

[asharppresidio]

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.

[MarcoWedel]

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

[LaetDcq]

It's seems to work with that code.

Mid(objectGUID,7,2) & Left(objectGUID,1) & Mid(objectGUID,6,1) & Mid(objectGUID,3,3) & Mid(objectGUID,2,1) &'-'& Mid(objectGUID,11,2)&
Mid(objectGUID,9,2)&'-'&Mid(objectGUID,15,2) & Mid(objectGUID,13,2) &'-'&Mid(objectGUID,17,4)&'-'&Mid(objectGUID,21)

Regards,