Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

String from excel to date in Qlikview

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

Date(Date#(MID(FIELDNAME,34,8), 'YYYYMMDD')) AS DATEFIELD


if the excel format is different from YYYYMMDD, change it

View solution in original post

12 Replies
mohammadkhatimi
Partner - Specialist
Partner - Specialist

Hie...


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



Hope this will helps you...!!

Regards,

Mohammad

Not applicable
Author

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
Master III
Master III

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
Master III
Master III

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Kushal_Chawda

How your FIELDNAME data looks like?

maxgro
MVP
MVP

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 #).

Not applicable
Author

I corrected it now to 8 chars.

My apologies.