topic Re: How to apply 'Prefix' in QlikView

How to apply 'Prefix'

dhanu_today — Thu, 12 Jun 2014 05:04:05 GMT

Hi Everyone,

I have field SEC_ACCT_NO which contains more than 15 digits and less than 15 digits. We have to show last 15 digits for that am using the ‘RIGHT’ function to get the 15 digits only.But how we can put Prefix’Zeros’ which are having less than 15 gitits.

Is their any function available in QV like Lpad,rpad in oracle.

The same has been attached.

Thanks,

Dhanu

Re: How to apply 'Prefix'

SergeyMak — Thu, 12 Jun 2014 05:12:03 GMT

Hi Dhanu,

As I understood you need a result like everything in the same format without zeroes.

If so, you can use simple IF in the load script like

IF(len(SEC_ACCT_NO)>15,RIGHT(SEC_ACCT_NO,15),SEC_ACCT_NO) AS YourField

Re: How to apply 'Prefix'

tresB — Thu, 12 Jun 2014 05:16:57 GMT

Try:

Repeat('0',15-Len(SEC_ACCT_NO))&Right(SEC_ACCT_NO,15)

Re: Re: How to apply 'Prefix'

SergeyMak — Thu, 12 Jun 2014 05:17:41 GMT

So, probably I caught your idea try this in your load script

PFA an example. I've done it for a dimension.

IF(len(SEC_ACCT_NO)>15,RIGHT(SEC_ACCT_NO,15),REPEAT('0',15-LEN(SEC_ACCT_NO))&SEC_ACCT_NO) AS YourField

Re: How to apply 'Prefix'

ashfaq_haseeb — Thu, 12 Jun 2014 05:36:43 GMT

Hi may be like this.

Regards

ASHFAQ

Re: How to apply 'Prefix'

anbu1984 — Thu, 12 Jun 2014 05:41:43 GMT

Like this..

Right(Repeat('0',15) & SEC_ACCT_NO,15)

Re: How to apply 'Prefix'

rbecher — Thu, 12 Jun 2014 05:44:20 GMT

Hi,

I would use:

Right(Repeat('0',15) & Text(SEC_ACCT_NO) ,15)

- Ralf

Re: How to apply 'Prefix'

dhanu_today — Fri, 13 Jun 2014 05:53:24 GMT

Thanks Tresseco and all ...Its working.But am getting the 15 digits as zero's even though the SEC_ACCT_NO having '-'.i have to show if its as itself like '-'.

Thanks,

Dhanu

Re: How to apply 'Prefix'

tresB — Fri, 13 Jun 2014 05:57:51 GMT

Could you post a sample that demonstrates the issue?

Re: Re: How to apply 'Prefix'

dhanu_today — Fri, 13 Jun 2014 06:09:06 GMT

Thats True.Any how am adding the sample app.

Re: How to apply 'Prefix'

preminqlik — Fri, 13 Jun 2014 06:21:26 GMT

hi dhanu i think u need this

=num(right(trim(SEC_ACCT_NO),15),'000000000000000')

Re: How to apply 'Prefix'

tresB — Fri, 13 Jun 2014 06:26:55 GMT

Try:
If(Len(SEC_ACCT_NO)>0, Repeat('0',15-Len(SEC_ACCT_NO))&Right(SEC_ACCT_NO,15) ,SEC_ACCT_NO) AS SEC_ACCT_NO_DERIVED