topic Extracting a String from a Larger String in QlikView

Extracting a String from a Larger String

Anonymous — Thu, 21 Oct 2010 02:26:16 GMT

Ok, I worked on this quite a bit today using the various examples I found in the forums but I still can't get the result I need.

I need to extract the name of the last OU value before 'OU=Enterprise' is encountered. The number of OUs in the string can vary so trying to find the logic to deal with that is killing me.

In the example above, I need my chart dimension to give me 'Food', 'Food' and 'Deskless Workers'.

Extracting a String from a Larger String

disqr_rm — Thu, 21 Oct 2010 03:17:22 GMT

A quick and not-so-dirty solution:


AD:
LOAD * INLINE [
ActiveDirectoryString
'CN=Alpine\,Patricia,OU=Users,OU=123,OU=Cladwell,OU=Food,OU=Enterprise,DC=na,DC=simplot,DC=com'
'CN=Anderson\,Beverly G,OU=Plant Users,OU=Cladwell,OU=Food,OU=Enterprise,DC=na,DC=simplot,DC=com'
'CN=Anderson\,Misti,OU=A-H,OU=Deskless Workers,OU=Enterprise,DC=na,DC=simplot,DC=com'
];
ADNew:
LOAD if(x1='OU=Enterprise', replace(previous(x1), 'OU=', '')) as Dimension;
LOAD subfield(ActiveDirectoryString, ',') as x1
RESIDENT AD;
DROP Table AD;

I am sure there are many other fancier ways to do the same.

Hope this helps.

Extracting a String from a Larger String

Thu, 21 Oct 2010 03:41:26 GMT

You can either put this on Dimension expression or load script. Will only work if the last instance of OU=<Value> is beside OU=Enterprise. I hope this helps.

TextBetween(<Your String Column>, ',OU=', ',OU=Enterprise',substringcount(<Your String Column>,',OU=')-1)

Extracting a String from a Larger String

Anonymous — Thu, 21 Oct 2010 05:52:26 GMT

Thanks pinongs, that was perfect. As with everything in QlikView, the solution was far easier than I thought it would be.