Finding out the length of a string

[#1]

Hi All,

I have a field in excel which consists of values in DD-MM-YYYY hh:mm:ss TT Format.

I wish to find out the length of the number of characters present in the field value in the script.

e.g. if value is 12/12/2014 01:10:00 AM , then length should be 22.

Thanks,

Asma

[hic]

The len function will measure the length of the field value as it is read. And this field  - when I try it - will be 20 chars long. See below.

But you maybe add this format in the list box? And have a shorter format in the source?

HIC

[petter]

A good general rule is always to trim your text/character data before you do something with them. That is trimming away leading and trailing white-space. Some - actually many - databases depending on column definitions and setting pad their character data so they will always return the maximum length of certain columns padded with spaces.

Use Trim() in QlikView to achieve this.

Len(Trim(myfield))

[MarcoWedel]

sample data

[MarcoWedel]

maybe this helps:

regards

Marco

[#6]

That is what is happening.

It is loading a numerical value from excel that is formatted as timestamp in the script. It is then counting the digits in the numerical representation.

In the excel file, the values displayed are in datetime format. But while reading it into Qlikview, it is read as numeric.

Is I possible to read the values in Qlikview in the datetime format which is present in the excel.

[MarcoWedel]

What are you using the length for?

[#8]

My basic requirement is to compare the datetime format of the values present in that field in the excel against a specific value.

If datetime format is 'DD/MM/YYYY hh:mm:ss TT', then Y else N would be stored in a new field DateTimeCheck.

I was not able to compare the timestamp format when I use the expression:

if(isnull(timestamp(timestamp#(datetimefield,'DD/MM/YYYY hh:mm:ss TT')))=-1,'Y','N')

It always returns 'N' eventhough the values present in the excel are in the format 'DD/MM/YYYY hh:mm:ss TT'.

So I thought of comparing the length of the values present in the field.

[MarcoWedel]

do you have to use excel as a source or can you convert to something else?

[swuehl]

I was not able to compare the timestamp format when I use the expression:

if(isnull(timestamp(timestamp#(datetimefield,'DD/MM/YYYY hh:mm:ss TT')))=-1,'Y','N')

It always returns 'N' eventhough the values present in the excel are in the format 'DD/MM/YYYY hh:mm:ss TT'.

Why are you using isnull() function here? Looking at your other thread, wasn't isnum() function suggested?

(Which would also make more sense given the then / else branch values).

[#11]

I tried using isnum() function as well, but then also it always returns N