Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
See why Qlik was recognized for the seventh year in a row – and discover how we can help you tackle your data integration challenges. Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:

My_Data:

LOAD

     postid,

     createddate,

     servicename,

     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)

FROM

(qvd);

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.

hic‌ !

1 Solution

Accepted Solutions
PrashantSangle

Hi,

You can not use newly created field in same load

try like

Year(timestamp#(scheduleddate,'DD-MM-YY hh:mm')) as schedule_date_year


and similar for other fields.


Regards

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

5 Replies
PrashantSangle

Hi,

You can not use newly created field in same load

try like

Year(timestamp#(scheduleddate,'DD-MM-YY hh:mm')) as schedule_date_year


and similar for other fields.


Regards

Great dreamer's dreams never fulfilled, they are always transcended.
manucamon
Specialist III
Specialist III

Hi Manoj, your timestamp format is not correct, try this:

My_Data:

LOAD

     postid,

     createddate,

     servicename,

     scheduleddate,

     cancelleddate,

     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,

     Year(finisheddate) as Finished_Year,

     Month(finisheddate) as Finished_Month,

     Day(finisheddate) as Finished_Day;

LOAD

     postid,

     createddate,

     servicename,

     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

FROM

(qvd);

Regards!!

sunny_talwar

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

Not applicable
Author

Thank you the quick reply. The data is in the exact same format as I have mentioned in my question above. Here are few rows from the data. Here the year is 2014.

 

17-10-14 16:18
17-10-14 17:22
21-10-14 12:42
21-10-14 14:30
21-10-14 16:07
21-10-14 16:10
21-10-14 16:27
21-10-14 16:42
22-10-14 14:46
22-10-14 15:00
23-10-14 10:28

23-10-14 10:50.

Not applicable
Author

Thank you.

It's worked for me.