String from excel to date in Qlikview

[#1]

Hi there,

I am trying to convert the following string from excel to a date value in Qlikview in the edit script as such:

DATE(MID(FIELDNAME,34,8), 'YYYY/MM/DD') AS DATEFIELD

However, it is not returning any values.

Could anyone be of assistance please?

Than you.

[mohammadkhatimi]

Hie...

Date(num#(Feildname,'Current format of date'),'Which format you want') as Date

Hope this will helps you...!!

Regards,

Mohammad

[#3]

Thank you for your reply.

Could this script be used if the date in excel in is the middle of a string value?

DATE(MID(FIELDNAME,34,4), 'YYYY/MM/DD') AS DATEFIELD

[sasiparupudi1]

if your string in the format 'YYYY/MM/DD' then

DATE#(MID(FIELDNAME,34,4), 'YYYY/MM/DD') AS DATEFIELD

else

DATE#(MID(FIELDNAME,34,4), 'your format') AS DATEFIELD

hth

Sasi

[sasiparupudi1]

yes mid function can be used in qlikview. below is the qv help

mid(s, n1[, n2 ])

Substring of the string s. The result is the string starting at character n1 with the length of n2 characters. If n2 is omitted, the function returns the rightmost part of the string starting at character n1. The positions in the string are numbered from 1 and up.

Example:

mid('abcdef',3 ) returns 'cdef'.

mid('abcdef',3, 2 ) returns 'cd'.

mid( Date,3 ) where Date = 970714 returns 0714.

mid( Date,3,2 ) where Date = 970714 returns 07.

For a more complicated example, see the index function below.

[jonathandienst]

It would be more useful if you showed us what your source data looked like. Otherwise we are just guessing.

[Kushal_Chawda]

How your FIELDNAME data looks like?

[maxgro]

could you post some example of your excel string?

from what I understand you're loading in Qlik, (with mid(FIELDNAME, 34,4)) 4 char from an excel file

[Digvijay_Singh]

You are loading 4 chars from source, to allow Qlikview to consider it as date, it needs to know your format thru Date# function, once QV clears first hurdle of taking in as a date in number form using Date#, it can be later formatted in any form using Date function(Without #).

[#10]

I corrected it now to 8 chars.

My apologies.