topic Re: Formate Date in QlikView

Formate Date

caniwhz1 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a problem with transferring a date column to QlikView.

In the original file, it is in YYYY / MM / DD format. When transferred to QlikView, the column is displayed in the list box (or also in the table) as follows [YYYY / MM / DD]. Filterings or calculations are thus not possible (because of the square bracket). Reformatting in QlikView did not work either. In the original date format I can not change anything, since I read this from another program.

My questions:

Is it possible to decompose the date column in year, month and day so that I can see three columns?

Or.

How can I determine when importing the file to QlikView that I only import the data for a given year.

Thanks in advance for the support. I have not worked very long with QlikView, so my knowledge in some areas is still not so great.

Cathleen N.

Re: Formate Date

qlikviewwizard — Wed, 18 Jan 2017 08:49:36 GMT

Hi,

Check your default script format.

Re: Formate Date

surendraj — Wed, 18 Jan 2017 08:55:05 GMT

Hi,

it is possible to decompose the date column in year, month

for month--> MONTH([date]

for YEAR-->YEAR([date])

and you can format your dates as you like by

Date(Date#(DateField, ' YYYY/MM/DD'), ' YYYY/MM/DD') as CustomDate

Re: Formate Date

tresB — Wed, 18 Jan 2017 08:55:40 GMT

Try loading your dates like:

Load

Date(Date#(DateField, 'YYYY/MM/DD'), 'YYYY/MM/DD') as MyDate,

Year(Date#(DateField, 'YYYY/MM/DD')) as MyYear,

Month(Date#(DateField, 'YYYY/MM/DD')) as MyMonth

Re: Formate Date

Colin-Albert — Wed, 18 Jan 2017 09:00:31 GMT

Have a look at this blog. Why don’t my dates work?

Re: Formate Date

caniwhz1 — Wed, 18 Jan 2017 09:56:00 GMT

Thanks.

The script format is in the right form:

SET DateFormat='YYYY.MM.DD';

The main Problem is, that the Date in the list box is in my opinion shown as text:

Re: Formate Date

caniwhz1 — Wed, 18 Jan 2017 10:03:53 GMT

Thank you.

I can now decompase the date column.

The main problem further on is that qlikview does not recognize my original column as a date column

Re: Formate Date

hemanthaanichet — Wed, 18 Jan 2017 10:16:12 GMT

Hi Cathleen Nier ,

I think you have to do data cleansing in the edit script

Replace the Square brackets with empty

May like below

repalce(repalce(datecolumn ,'[',''),']','') as DateColumn

Regards

Hemanth

Re: Formate Date

Anonymous — Wed, 18 Jan 2017 10:21:52 GMT

Hi,

If i'm correct your query is that , u need to split year,month,day from your original file & see them as different columns in your dashboard.

First,ensure that in script editor file,your date format should be like -SET DateFormat='M/D/YYYY'.
Now load your date field thrice

[Date] as date 1

[Date] as date 2

[Date] as date 3

Reload the data and in List box properties,u can override document settings
Select Number and provide correct precision and extract required fields(year/month/day).
Or else, u can select Date & give conditional statement as Month[date],year[date].

Thank You..!

Re: Formate Date

susovan — Wed, 18 Jan 2017 10:39:46 GMT

You can also check following links.

1) https://community.qlik.com/blogs/qlikviewdesignblog/2014/12/02/the-date-function

2) http://qlikshare.com/using-alt-function-qlikview/

Re: Formate Date

caniwhz1 — Wed, 18 Jan 2017 10:42:32 GMT

Thank you.

Only one question:

On which point I insert the command?

SET TimeFormat='hh:mm:ss';

SET DateFormat='YYYY.MM.DD';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

...

Directory;

LOAD

Date(Date#(StartTermin, 'YYYY/MM/DD'), 'YYYY/MM/DD') as MyDate,

Year(Date#(StartTermin, 'YYYY/MM/DD')) as MyYear,

Month(Date#(StartTermin, 'YYYY/MM/DD')) as MyMonth

FROM

Re: Formate Date

Anonymous — Wed, 18 Jan 2017 10:53:15 GMT

try this

load

Date(Date#(YourDateField),'YYYY/MM/DD') as NewDate;

from XXX

Re: Formate Date

MindaugasBacius — Wed, 18 Jan 2017 11:00:55 GMT

Date(Date#(mid('[2016/12/30]', 2, 10), 'YYYY/MM/DD')) as Date_new

Instead of '[2016/12/30]' use your date field in script, this may work.

The previous code returns:

Re: Formate Date

caniwhz1 — Wed, 18 Jan 2017 11:32:41 GMT

Thank you so much. !!!!!!!!!!!!!!!!!!!!!!!!!!!!

It works

Re: Formate Date

MindaugasBacius — Wed, 18 Jan 2017 11:32:55 GMT

LOAD

Date(Date#(mid(StartTermin, 2, 10), 'YYYY/MM/DD')) as Date_new

,Year(Date(Date#(mid(StartTermin, 2, 10), 'YYYY/MM/DD'))) as Year_new

,Month(Date(Date#(mid(StartTermin, 2, 10), 'YYYY/MM/DD'))) as Month_new

FROM

Re: Formate Date

caniwhz1 — Wed, 18 Jan 2017 12:58:38 GMT

Thank you for the help