topic Re: Length of field in QlikView

Length of field

coolwaters — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Length of field

BalaBhaskar_Qlik — Sat, 01 Jul 2017 00:49:22 GMT

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

Re: Length of field

coolwaters — Sat, 01 Jul 2017 02:26:10 GMT

Hi Bala,

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

Thanks.

Re: Length of field

devarasu07 — Sat, 01 Jul 2017 04:48:26 GMT

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 [

00000000112

00002327004

00002327030

51079094356

51079094420

51079094708

03076803568

06318953300

07431230413

6035131

];

Re: Length of field

Kushal_Chawda — Sat, 01 Jul 2017 05:33:20 GMT

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

Re: Length of field

coolwaters — Sat, 01 Jul 2017 05:49:01 GMT

Hi Devarasu,

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

Thanks.

Re: Length of field

coolwaters — Sat, 01 Jul 2017 05:53:03 GMT

Hi Kushal,

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

Re: Length of field

Kushal_Chawda — Sat, 01 Jul 2017 06:14:54 GMT

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.

Re: Length of field

antoniotiman — Sat, 01 Jul 2017 06:25:57 GMT

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

Re: Length of field

passionate — Sat, 01 Jul 2017 09:28:23 GMT

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

Re: Length of field

coolwaters — Sun, 02 Jul 2017 15:12:04 GMT

Hi Antonio,

Works, thanks again for all your help.