Length of field

[coolwaters]

Hi,

A column in excel has ID field with numbers. Some begin with 0's.

I am able to get the data into QlikView with the 0's which is correct. But i also need to know the length of the field and the 0's are getting ignored when using LEN(). I need the length considering the 0's which should be 11 for all the ID's.

I have tried using Text() but strangely it is trimming the 0's.

Thanks.

[balabhaskarqlik]

Try this:

LOAD *,

    Len(Fields) as FieldLen,

    Text(Fields) as FieldTe, // Or Use Keepchar function

    KeepChar(Fields,'0123456789') as FieldK;

Length:

LOAD * Inline

[

Fields

00000000112

00002327004

00002327030

51079094356

51079094420

51079094708

03076803568

06318953300

07431230413

00006035131

];

And this is the o/p i'm getting

[coolwaters]

Hi Bala,

Please try using the excel attached. I still have the same issue. 

Thanks.

[devarasu07]

Hi,

What is the issue are you getting. len() function is working fine in back/front end. refer below is based on your excel source.

load *, len(ID);

LOAD * INLINE [

    ID

    00000000112

    00002327004

    00002327030

    51079094356

    51079094420

    51079094708

    03076803568

    06318953300

    07431230413

    6035131

];

[Kushal_Chawda]

One solution could be, change your ID field format of your excel to Text, then enter the values and load it n qlikview

[coolwaters]

Hi Devarasu,

Request you to import the data from the excel attached. With Inline Load there is no issue.

Thanks.

[coolwaters]

Hi Kushal,

By changing the format of the field in excel to text the starting 0's disappear.

[Kushal_Chawda]

How you are getting dump in excel file? If you are getting it from IT team, ask them to give this column in text format. They must be giving this dump by querying the database.

[antoniotiman · Accepted solution]

Hi,

Temp:
LOAD ID
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1298892-285531/Number%20Test.xlsx"
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
LOAD ID,Len(Text(ID)) as Len;
LOAD Text(ID) as ID Resident Temp;
Drop Table Temp;

Regards,

Antonio

[passionate]

Create a column which has zero replaced by 1 and the find length of this column

replace(Columnname, '0', ' 1') as newcolumn

Find Len (Newcolumn) and drop newcolumn.

Do this in load script