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

[#2]

Yes, the source should be an excel file

[swuehl]

So why don't you upload a samle excel file.

You can remove any other columns if needed, but I think it would be really helpful to see your date parsing issue coming live.

[#4]

Please find the sample dtata

[MarcoWedel]

Hi Asma,

besides this one you have some open threads that could be answered correctly.

Please take a look and close the ones that are answered correctly.

thanks

regards

Marco

[MarcoWedel]

another example using your sample file:

tabDates:
LOAD Date,
    Len(Date) as LenDate,
    Len(Text(Date)) as LenTextDate,
    Num(Date) as NumDate,
    Len(Num(Date)) as LenNumDate
FROM [https://community.qlik.com/servlet/JiveServlet/download/791328-168464/SampleFile.xls](biff, embedded labels, table is Sheet1$);

It seems like Len(Date) only calculates the length of the numerical representation of the excel timestamps and therefore cannot be used to test the timestamp format.

regards

Marco

[swuehl]

Asma,

you're attached Excel shows a Date column that's actually numbers formatted as Timestamp (and actually, if I don't miss something, it's formatted as 'DD.MM.YYYY hh:mm').

I think what's going on when loading this field into QV is following:

QV will only read the numbers, and use cell format from the Excel file to tag that field as timestamp. This allows QV to show a textual representation as defined by your default timestamp format (in script or by referencing the OS setting),but without adding this formatting to the actual symbol table

(and I think this is also showing the mem stats file

Database

Field

Symbols

Date

37583

2891

13.00

).

It seems to me that's your field Date is still only a number, using

     isnum(Date) as IsNum,

     istext(Date) as IsText,

will return only TRUE for IsNum.

Also

     text(Date) as TextDate

will only return the decimal number as text, not the number formatted as timestamp.

Assuming all this is true, using a interpretation function like Timestamp#() will obviously not work.

I believe hic‌ can easily confirm this or add another explanation.

Currently, I see no easy way to obtain the cell format from the excel file, if this is what you are really interested in.

But maybe your date format comparison requirement is about something else, that needs a complete different solution.

Could you describe a bit more detailed what your next steps would be, using the 'Y' / 'N' flag?