How to format a cell with fixed characters!

[nevilledhamsiri]

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

[m_woolf]

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,

[nevilledhamsiri]

Thanks MW,

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

Thanks

Neville

[Frank_Hartmann · Accepted solution]

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

LOAD * INLINE [

    REF_CODE

    2535

    6530

    7523

    12545

    15128

];

[nevilledhamsiri]

Dear Frank,

This is alright

Thanks

Neville

[m_woolf]

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,