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: 
caniwhz1
Contributor III
Contributor III

Formate Date

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.

1 Solution

Accepted Solutions
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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:

Screenshot_1.jpg

View solution in original post

15 Replies
qlikviewwizard
Master II
Master II

Hi,

Check your default script format.

Capture.PNG

surendraj
Specialist
Specialist

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

tresesco
MVP
MVP

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


Colin-Albert

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

caniwhz1
Contributor III
Contributor III
Author

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:

Date.JPG

caniwhz1
Contributor III
Contributor III
Author

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

hemanthaanichet
Creator III
Creator III

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

Anonymous
Not applicable

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).
  • solu.PNG
  • Or else, u can select Date & give conditional statement as Month[date],year[date].

Thank You..!

susovan
Partner - Specialist
Partner - Specialist