topic Substring the date to select only year in QlikView

Substring the date to select only year

Wed, 09 Nov 2016 09:54:37 GMT

Hello all,

I'm very new to Qlikview and need your help to extract only a year from the date.

My source data is simple excel. Order date in the format mm/dd/yyyy. I'm using below Subfield function to extract only year from order date and ship date. When I reload the data and preview the table, it doesnt show any year in loaded table preview.

Please help me if I'm missing anything here. Thanks in advance. Below is the SQL im trying to load.

Please let me know if any addition info required.

NoConcatenate LOAD [Row ID],

[Order ID],

[Order Date],

SubField([Order Date],'/',3) AS Saleyear,

[Ship Date],

SubField([Ship Date],'/',3) AS Shipmentyear,

[Ship Mode],

FROM

Re: Substring the date to select only year

Wed, 09 Nov 2016 11:31:46 GMT

You can use the below in script

LOAD [Row ID],

[Order ID],

[Order Date],

Year([Order Date]) as Year,

Month([Order Date]) as Month,

[Ship Date],

Year([Ship Date]) as Year,

Month([Ship Date]) as Month,

like this you can derive

Re: Substring the date to select only year

sunny_talwar — Wed, 09 Nov 2016 12:42:49 GMT

First thing to check would be to see if [Order Date] and [Ship Date] are read correctly by QlikView. To learn how to check this look here:

Why don’t my dates work?‌

Get the Dates Right‌

Once the dates are read correctly, simply use the Year() function to extract date from your Date fields (just like pointed out by arumamahesh‌)

Re: Substring the date to select only year

Wed, 09 Nov 2016 18:38:38 GMT

Excellent.It worked. Thanks Umamaheshwara and Sunny