topic Re: Substr and Instr in Qlikview? in QlikView

Substr and Instr in Qlikview?

Wed, 02 Nov 2016 21:38:54 GMT

Having a tough time translating this SQL function to work as a load in Qlikview:

lower(substr(EMAIL_ADDRESS,instr(EMAIL_ADDRESS,'@'),length(EMAIL_ADDRESS))) "Email Domain"

essentially I'm loading just the @xxxxx.xxx of the email.

Any help would be greatly appreciated, thanks!

-Jason

maxgro — Wed, 02 Nov 2016 22:03:44 GMT

load

....,

lower(mid('alfa.beta@gmail.com', index('alfa.beta@gmail.com', '@'), len('alfa.beta@gmail.com'))) as [Email Domain],

....

from

....

replace 'alfa.beta@gmail.com' with EMAIL_ADDRESS

MarcoWedel — Wed, 02 Nov 2016 22:48:58 GMT

Hi,

instead of the "literal" translation to QlikView functions you could implement this function like this:

Lower(Mid(EMAIL_ADDRESS,Index(EMAIL_ADDRESS,'@')))

(using the 2 parameter version of the Mid() function)

or if there is always one '@':

Lower(SubField(EMAIL_ADDRESS,'@',2))

(not including the '@' in the result, but you might rather be interested in the domain name anyway, if not just preced with '@'&)

hope this helps

regards

Marco