topic How to format a cell with fixed characters! in QlikView

How to format a cell with fixed characters!

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

Hi, Dear all,

Please help me to achieve following objectives!

1st Scenario!

I have set of employees whose ref_numbers are varied. It is said that every code should carries 5 characters. Hence all numbers less than 5, need to have zero value in front of them.

2nd Scenario!

If I need to add zero value behind the numbers which are less than 5 characters how that be achieved!

Thanks

Neville

Re: How to format a cell with fixed characters!

m_woolf — Fri, 06 Oct 2017 17:27:04 GMT

This is best done in load script.

Scenario 1:

if(Len(REF_CODE)=5,REF_CODE,repeat('0',5-len(REF_CODE)) & REF_CODE) as REF_CODE,

Re: How to format a cell with fixed characters!

nevilledhamsiri — Fri, 06 Oct 2017 17:36:29 GMT

Thanks MW,

1st Scenario turns alright!, How I will achieve the 2nd scenario?

Thanks

Neville

Re: How to format a cell with fixed characters!

Frank_Hartmann — Fri, 06 Oct 2017 17:59:39 GMT

Load *, if(len(REF_CODE)<5,REF_CODE&0,REF_CODE) as RefCodeNew;

LOAD * INLINE [

REF_CODE

2535

6530

7523

12545

15128

];

Re: How to format a cell with fixed characters!

nevilledhamsiri — Fri, 06 Oct 2017 18:37:27 GMT

Dear Frank,

This is alright

Thanks

Neville

Re: How to format a cell with fixed characters!

m_woolf — Mon, 09 Oct 2017 12:20:04 GMT

Frank's answer will work only for numbers of 4 digits.

Scenario 2:

if(Len(REF_CODE)=5,REF_CODE, REF_CODE & repeat('0',5-len(REF_CODE))) as REF_CODE,