Having Problem in finding year and date from date. Output year is the date of the month from the data...

[#1]

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‌ !

[PrashantSangle · Accepted solution]

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

[Anonymous]

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

[#5]

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.

[#6]

Thank you.

It's worked for me.