Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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
MVP
MVP

Re: String from excel to date in Qlikview

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


if the excel format is different from YYYYMMDD, change it

12 Replies
Partner
Partner

Re: String from excel to date in Qlikview

Hie...


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



Hope this will helps you...!!

Regards,

Mohammad

Not applicable

Re: String from excel to date in Qlikview

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
Honored Contributor III

Re: String from excel to date in Qlikview

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
Honored Contributor III

Re: String from excel to date in Qlikview

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.

MVP
MVP

Re: String from excel to date in Qlikview

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

Re: String from excel to date in Qlikview

How your FIELDNAME data looks like?

MVP
MVP

Re: String from excel to date in Qlikview

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
Honored Contributor III

Re: String from excel to date in Qlikview

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

Re: String from excel to date in Qlikview

I corrected it now to 8 chars.

My apologies.