Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
Hi,
Check your default script format.
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
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
Have a look at this blog. Why don’t my dates work?
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:
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
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
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.
[Date] as date 1
[Date] as date 2
[Date] as date 3
Thank You..!
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/