topic value with trailing numbers in QlikView

value with trailing numbers

Fri, 09 Dec 2011 16:00:59 GMT

Currently Im having issues with this piece of code. The first and second lines on the if statement are outputting correctly ( '40050101' changing to '2012' and '40020201' to '2014').

But the last 4 are not.

Take the numcer '400401' for example. In the field @17 there are a number of values beginning with '400401'. For instance I have '40040100', '40040101', '40040102' and '40040103' - I think this is why it doesnt like the value '400401'

I've used the trim and rtrim functions to help but it still doesnt seem to work. Would anyone know how to overcome this issue? Thanks

if(trim(@17) = '40050101', '2012',

if(trim(@17) = '40020201', '2014',

if(trim(@17) = '400401', '2011',

if(trim(@17) = '4005', '2009',

if(trim(@17) = '4006', '2040',

if(trim(@17) = '40010100', '2001')))))) as bdg_link,

value with trailing numbers

swuehl — Fri, 09 Dec 2011 16:16:59 GMT

Maybe try the like operator together with a wildcard:

...

if( trim(@17) like '400401*', '2011'),

...

Or try using left() function

if( left(trim(@17),6) = '400401', '2011'),

The trim function removes leading and trailing spaces only, so you don't need it if there are no spaces.

hope this helps,

Stefan

Re: value with trailing numbers

Fri, 09 Dec 2011 16:18:02 GMT

Try using Left function

if(left(@17,8) = '40050101', '2012',

if(left(@17,8) = '40020201', '2014',

if(left(@17,6) = '400401', '2011',

if(left(@17,4) = '4005', '2009',

if(left(@17,4) = '4006', '2040',

if(left(@17,8) = '40010100', '2001')))))) as bdg_link,

Edit: And once again I type to slow haha. See Above that should work