topic Third last numeric character in QlikView

Third last numeric character

tarynnagel — Thu, 08 Feb 2018 07:49:18 GMT

Hi, I need to identify a specific number that matches to certain criteria. This criteria is that is must be 10 digits long and the third last number must be a three (3).

e.g.

123456789

1541162347

98765327

The second one should be a match and will return 'YES' in a new field.

Can anyone assist?

Re: Third last numeric character

zhadrakas — Thu, 08 Feb 2018 07:53:17 GMT

maybe this

if(len(FIELD) = 10 And Isnumeric(FIELD) AND mid(FIELD,8,1)=3, 'YES', 'NO') as NEW_FIELD

Re: Third last numeric character

ali_hijazi — Thu, 08 Feb 2018 07:53:31 GMT

if(len(your_field) = 10 and mid(right(your_field,3),1) = '3' and isNumeric(your_field),1,0)

Re: Third last numeric character

MarcoWedel — Thu, 08 Feb 2018 22:22:01 GMT

number like '???????3??'

Re: Third last numeric character

MarcoWedel — Thu, 08 Feb 2018 22:26:06 GMT

one example:

table1:
LOAD *,
    If(number like '???????3??','Yes','No') as Flag
INLINE [
    number
    123456789
    1541162347
    98765327
];

hope this helps

regards

Marco