topic Re: data format in QlikView

data format

Chanty4u — Thu, 08 Oct 2015 02:57:04 GMT

Hi all,

I have one excel file like

employee id

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

Re: data format

sunny_talwar — Thu, 08 Oct 2015 03:05:04 GMT

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

Re: data format

Chanty4u — Thu, 08 Oct 2015 03:10:51 GMT

thanks for ur response sunny..

i used

Text()

text(empid) as empid

but didnt worked? how can i?

Re: data format

sunny_talwar — Thu, 08 Oct 2015 03:13:44 GMT

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

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

Re: data format

SreeniJD — Thu, 08 Oct 2015 03:18:16 GMT

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;

Re: data format

Thu, 08 Oct 2015 03:21:02 GMT

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;

Re: data format

Chanty4u — Thu, 08 Oct 2015 03:25:28 GMT

thanks sunny and arjun..

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

Regards

Suresh chanty

Re: data format

saurabhwadhwa — Thu, 08 Oct 2015 03:32:23 GMT

Hi ,

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

Re: Keeping Leading Zeros

Thanks

Re: data format

settu_periasamy — Thu, 08 Oct 2015 04:16:39 GMT

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

Re: data format

Chanty4u — Thu, 08 Oct 2015 06:03:59 GMT

thanks all. its working fine. thanks settu.

Suresh

Re: data format

Chanty4u — Thu, 08 Oct 2015 06:48:22 GMT

thanks JD.