Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
kateevans2
New Contributor

Date Conversion Not Working

The original field being loaded is a text field in excel and shows as ,for example "01- JUL-13" i need this to be displayed as 01/07/13 and mappable to another date of the same format, however all the date conversion i have tried do not appear to amend the date format or will bring through a null value instead.

 

I have attempted to use the following 

M_NUMBER,

ND_DATE,
Date#(num#(ND_DATE),'DD/MM/YYYY') as ND_DATE2,
num(ND_DATE) as ND_DATE3,
date(num#(ND_DATE),'DD/MM/YYYY') as ND_DATE4,
Date(num#(ND_DATE), 'DD/MM/YYYY') as ND_DATE5
Date(Floor(ND_DATE), 'DD/MM/YYYY'')as ND_DATE6,
Date(ND_DATE, 'MM/DD/YYYY') as ND_DATE7,

Date(Date#(ND_DATE, 'DD-MMM-YYY), 'DD/MM/YYYY')as ND_DATE8

 

original Data format in attached file any assistance gladly received 

4 Replies

Re: Date Conversion Not Working

Try this

Table:
LOAD Date(Date#(ND_DATE, 'DD-MMM-YY')) as ND_DATE2,
	 ND_DATE,
     CLAIM_NUMBER
FROM
[..\..\Downloads\Dates.xlsx]
(ooxml, embedded labels, table is Sheet1);
ishanbhatt
Contributor II

Re: Date Conversion Not Working

Hello,

 

Use below formula.

Date(Floor(num(Date field)))  as Date.

Set Date format in the start.

kateevans2
New Contributor

Re: Date Conversion Not Working

Thank you the answer was a cross of your code and an earlier attempt i had

 

 

Date(Date#(ND_DATE,'DD-MMM-YY'), 'DD/MM/YYYY') 

 

many thanks for your help 

Re: Date Conversion Not Working

If you set your environmental variable with the required date

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;

All you would need to do is this

Date(Date#(ND_DATE,'DD-MMM-YY')) as ND_DATE

 

Community Browser