topic Convert GUID to correct format in QlikView

Convert GUID to correct format

asharppresidio — Tue, 09 Jan 2018 17:02:54 GMT

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

Re: Convert GUID to correct format

atoz1158 — Tue, 09 Jan 2018 17:14:56 GMT

In your load bit after the line

,lastLogon

you need to add

,AD_GUID

Re: Convert GUID to correct format

asharppresidio — Tue, 09 Jan 2018 17:28:49 GMT

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

MarcoWedel — Tue, 09 Jan 2018 21:35:18 GMT

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

Re: Convert GUID to correct format

LaetDcq — Tue, 10 May 2022 09:30:02 GMT

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,