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?
Solved! Go to Solution.
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
if your string in the format 'YYYY/MM/DD' then
DATE#(MID(FIELDNAME,34,4), 'YYYY/MM/DD') AS DATEFIELD
DATE#(MID(FIELDNAME,34,4), 'your format') AS DATEFIELD
yes mid function can be used in qlikview. below is the qv help
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.
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.
It would be more useful if you showed us what your source data looked like. Otherwise we are just guessing.
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 #).