topic Re: String from excel to date in Qlikview in QlikView

String from excel to date in Qlikview

Tue, 25 Aug 2015 06:11:05 GMT

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.

Re: String from excel to date in Qlikview

mohammadkhatimi — Tue, 25 Aug 2015 06:13:31 GMT

Hie...

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

Hope this will helps you...!!

Regards,

Mohammad

Re: String from excel to date in Qlikview

Tue, 25 Aug 2015 06:16:56 GMT

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

Re: String from excel to date in Qlikview

sasiparupudi1 — Tue, 25 Aug 2015 06:18:04 GMT

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

Re: String from excel to date in Qlikview

sasiparupudi1 — Tue, 25 Aug 2015 06:19:23 GMT

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.

Re: String from excel to date in Qlikview

jonathandienst — Tue, 25 Aug 2015 06:20:38 GMT

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

Re: String from excel to date in Qlikview

Kushal_Chawda — Tue, 25 Aug 2015 06:23:39 GMT

How your FIELDNAME data looks like?

Re: String from excel to date in Qlikview

maxgro — Tue, 25 Aug 2015 06:27:50 GMT

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

Re: String from excel to date in Qlikview

Digvijay_Singh — Tue, 25 Aug 2015 06:45:06 GMT

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

Re: String from excel to date in Qlikview

Tue, 25 Aug 2015 06:59:05 GMT

I corrected it now to 8 chars.

My apologies.

Re: String from excel to date in Qlikview

maxgro — Tue, 25 Aug 2015 07:07:39 GMT

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

if the excel format is different from YYYYMMDD, change it

Re: String from excel to date in Qlikview

sujeetsingh — Tue, 25 Aug 2015 07:14:13 GMT

Please post sample

Re: String from excel to date in Qlikview

Wed, 26 Aug 2015 12:03:18 GMT

Thank you all for assistance