data format

[Chanty4u]

Hi all,

I have one excel file like

employee id

1

2

3

123

145

3000

30000

30001

aftr import into qlikview i did some calucatlations  and  after that field lik below

0000000001

0000000002

0000000003

0000000123

......

0000030001..

then am using store command and store it into.txt format in excel ...but i didnt get zeros in excel?

how can i achieve this help me on this?

suresh

[sunny_talwar]

May be use the Text() function after you do the calculations and before you transfer it out.

[Chanty4u]

thanks for ur response sunny..

i used

Text()

text(empid) as empid

but didnt worked?   how can i?

[sunny_talwar]

Again not sure if this would work, but give this a try:

Text(Num(empid, '00000000000')) as empid

[SreeniJD]

Hi Suresh,

As Sunny suggested, use Text(empid) this will sure work... You can use this in expression or in script as well..

T1:

LOAD * INLINE [

EMP_ID

0000000001

0000000002

0000000003

0000000123

0000030001

];

LOAD*,

TEXT(EMP_ID) AS NEW_EMP_ID

RESIDENT T1;

[#6]

QV interprets field as a number and once converted, similar to Excel the leading zero are lost.

I believe using Text() in your initial load will resolve the issue. Fields that look like numbers should be treated as text .

Use * but will need one extra field for text(Field Name) :

LOAD *,

     Text(Field Name) As FieldName;

FROM ...;

DROP Field FieldName;

RENAME Field Field Name to FieldName;

[Chanty4u]

thanks  sunny and arjun..

i will try and let u know.. if it is wrkng or not..

Regards

Suresh chanty

[saurabhwadhwa]

Hi ,

Your issue looks similar to thread #788112 .Please check the below link .

Re: Keeping Leading Zeros

Thanks

[settu_periasamy · Accepted solution]

Hi suresh,

One More approach

Excel will remove the leading zero's if you open the text in excel.

May be you can try this

after storing the data as text, change the extension as .CSV

Open new excel -> Data Tab -> Get External Data -> From Text

Select the CSV (you changed from .txt to .csv)

Text Import Wizard will appear, In the third step of wizard

Change the Column Data format  as Text

[Chanty4u]

thanks all. its working fine. thanks settu.

Suresh