Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
asharppresidio
Contributor
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'  ;

4 Replies
atoz1158
Creator II
Creator II

In your load bit after the line

,lastLogon

you need to add

,AD_GUID

asharppresidio
Contributor
Contributor
Author

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

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,