Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Chanty4u
Esteemed Contributor III

data format

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

1 Solution

Accepted Solutions

Re: data format

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

Capture.JPG

10 Replies

Re: data format

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

Chanty4u
Esteemed Contributor III

Re: data format

thanks for ur response sunny..

i used

Text()

text(empid) as empid

but didnt worked?   how can i?

Re: data format

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

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

SreeniJD
Valued Contributor

Re: data format

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;

Not applicable

Re: data format

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
Esteemed Contributor III

Re: data format

thanks  sunny and arjun..

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

Regards

Suresh chanty

Partner
Partner

Re: data format

Hi ,

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

Re: Keeping Leading Zeros

Thanks

Re: data format

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

Capture.JPG

Chanty4u
Esteemed Contributor III

Re: data format

thanks all. its working fine. thanks settu.

Suresh