How to get last 7 or 8 or 9...digits in qlikview.

[#1]

In sql SUBSTR(ACCOUNT_NO,-7) will give the last 7 digits.

Ex: 276-76288330 will become 6288330

I tried right(ACCOUNT_NO,7)

It is not working?Is there anything else I can try ?

[stabben23 · Accepted solution]

Hi, That should works, dont forget to do right(ACCOUNT_NO,7) as ACCOUNT_NO.

//Staffan

[swuehl]

right('276-76288330',7)

should work, returns 6288330 to me.

[rbecher]

To be save you should use:

right(text(ACCOUNT_NO), 7)

- Ralf

[Anonymous]

Function right() works:

right('276-76288330',7) returns 6288330

Can you show a non-working example?

[Colin-Albert]

If(index(ACCOUNT_NO, '-') =0,

   ACCOUNT_NO,

   right(ACCOUNT_NO, (index(ACCOUNT_NO, '-', -1) -1) )

) as ACCOUNT_NO

This tests if the account no contains '-', if not then the account no is used as is. If '-' is present, the second index command uses the -1 parameter to find the position of the dash counting from the end of the string, subtracts one from this figure, then uses this for the right command to extract the account number.

This will work for account number strings that have the dash in varying positions.

[#7]

Text(Right(Text,CNT))

[#8]

Hi

If you want to extract de number after the '-' you can use de subfields()  function

Example:

LOAD '1234-12341243-Field3' as text,

     SubField('1234-12341243-Field3', '-', 1) as f1,

     SubField('1234-12341243-Field3', '-', 2) as f2,

     SubField('1234-12341243-Field3', '-', 3) as f3

autogenerate 1;

you get:

  f1 = 1234

  f2 =12341234

  f3 = Field3

Regards