topic Re: Using Date#() Function to extract Month, Day, Year in QlikView

Using Date#() Function to extract Month, Day, Year

Thu, 01 Sep 2016 15:27:15 GMT

Hello all,

I have a field called "created_at". This field contains a date in YYYY-MM-DD format.

I am trying to extract out the year, month, and day using the Date#() format. I've been using this expression (to try and grab month):

	,Date#(ticket.created_at, 'YYYY-MM-DD') as Extracted_date
	,Month(Extracted_date) as Month

For some reason this just doesn't seem to be working, am I doing something wrong here?

Re: Using Date#() Function to extract Month, Day, Year

agomes1971 — Thu, 01 Sep 2016 15:35:28 GMT

Hi,

just to year(created_at) it will work and Month too.

Thanks

André Gomes

Re: Using Date#() Function to extract Month, Day, Year

sunny_talwar — Thu, 01 Sep 2016 15:48:38 GMT

Do the further extraction of month, year, day in the preceding load

LOAD Month(Extracted_date) as Month,

Year(Extracted_date) as Year,

LOAD *,

Date#(ticket.created_at, 'YYYY-MM-DD') as Extracted_date

FROM ....;

Re: Using Date#() Function to extract Month, Day, Year

Peter_Cammaert — Thu, 01 Sep 2016 16:00:25 GMT

Sunny is right. In query languages (QlikView script or SQL), you cannot use the column your created on one line in the expression of the next column creation line because the first column may not even exist yet. Column creations in a LOAD statement are almost never executed in the sequence that they are listed (optimizers mess up this sequence anyway) so you'll need another LOAD to make use of them.

Peter