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

[#1]

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?

[agomes1971]

Hi,

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

Thanks

André Gomes

[sunny_talwar]

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 ....;

[Peter_Cammaert]

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