Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
coolwaters
Contributor II
Contributor II

Length of field

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.

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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

View solution in original post

10 Replies
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
Contributor II
Contributor II
Author

Hi Bala,


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


Thanks.

devarasu07
Master II
Master II

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

];

Capture.JPG

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
Contributor II
Contributor II
Author

Hi Devarasu,


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


Thanks.



coolwaters
Contributor II
Contributor II
Author

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
Master III
Master III

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
Specialist
Specialist

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