Having Problem in finding year and date from date. Output year is the date of the month from the data...
Hi everyone! I'm new to qlikview and I have just started few days back. I am working with a data file which has date format like "17:10:14 17:22".
I want to find year, month, quarter, weekday, weekday etc but I'm unable to do that. I have gone through many posts here on Qlikview community having very similar issues, but it is not working for me. Let me explain in detail what I'm trying. My script is:
Date(timestamp#(scheduleddate,'DD-MM-YY hh:mm'), 'DD-MM-YYYY') as scheduleddate,
Date(timestamp#(cancelleddate,'DD-MM-YY hh:mm'), 'DD-MM-YYYY') as cancelleddate,
Date(timestamp#(finisheddate,'DD-MM-YY hh:mm'), 'DD-MM-YYYY') as finisheddate,
Year(scheduleddate) as Scheduled_Year,
Month(scheduleddate) as Scheduled_Month,
Day(scheduleddate) as Scheduled_Day,
Year(cancelleddate) as cancelled_Year,
Month(cancelleddate) as Cancelled_Month,
Day(cancelleddate) as Cancelled_Day,
.......(similar for finisheddate)
Now suppose if scheduleddate in the data is 31-01-15 17:22, in this case the output scheduleddate is 31-01-2015,Scheduled_Year is 2027, Scheduled_Month is Jan and Scheduled_Day is 15. What is happening is that the date of the month is being read as Year and the year as Date. I have searched for a long time, gone through many posts related to the date format but still it is not working for me. If anybody can help me here? Thanks in advance.
Is you date in this format -> 17:10:14 17:22 or this -> 31-01-15 17:22 or can it be a hybrid format? If it is hybrid, you might need to use Alt() function to read the date in proper way. Please provide few rows of sample data for the date field and we might be able to provide a much better help