topic Re: Having Problem in finding year and date from date. Output year is the date of the month from the data... in QlikView

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

Wed, 25 Nov 2020 16:16:04 GMT

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

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

PrashantSangle — Tue, 31 May 2016 12:11:55 GMT

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

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

Anonymous — Tue, 31 May 2016 13:32:43 GMT

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

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

sunny_talwar — Tue, 31 May 2016 13:42:30 GMT

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

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

Tue, 31 May 2016 14:10:29 GMT

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.

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

Tue, 31 May 2016 14:20:02 GMT

Thank you.

It's worked for me.