topic Re: How to convert date format DD-MM-YYYY into Year ? in QlikView

How to convert date format DD-MM-YYYY into Year ?

paulyeo11 — Wed, 17 Jan 2018 08:52:55 GMT

How to convert date format DD-MM-YYYY into Year ?

Hi All

I have import a raw data file from Excel to Qli View.

I try to convert the imported field name = [Expected Close Date] in order year format YYYY.

I have try to using the below expression :-

// text(right(Year(date#([Expected Close Date],'DD/MM/YYYY')), 2),'00') as Year,

// dual(right(Year(date#([Expected Close Date],'DD/MM/YYYY')), 2),'00') as Year,

// year(today()) - year([Expected Close Date]) + 1 as Year,

// num(right(Year(date#([Expected Close Date],'DD/MM/YYYY')), 2),'00') as Year,

// dual(right(Year(date#([Expected Close Date],'DD/MM/YYYY')), 2),'00') as Year,

// Right(year([Expected Close Date]),2) as Year,

All the above not able to work.

Can some one advise me.

Paul Yeo

Re: How to convert date format DD-MM-YYYY into Year ?

avinashelite — Wed, 17 Jan 2018 08:57:55 GMT

try like this

subfiled([Expected Close Date],'-' ,3) as Year

Re: How to convert date format DD-MM-YYYY into Year ?

paulyeo11 — Wed, 17 Jan 2018 09:01:04 GMT

Hi Avinash

Thank you for your sharing , i get below error :-

Error in expression:

SUBFILED is not a valid function

Paul

Re: How to convert date format DD-MM-YYYY into Year ?

Anil_Babu_Samineni — Wed, 17 Jan 2018 09:01:47 GMT

I would think this?

Year([Expected Close Date]) as Year

Re: How to convert date format DD-MM-YYYY into Year ?

paulyeo11 — Wed, 17 Jan 2018 09:04:10 GMT

Hi Anil

Thank you for your sharing again.

Your no error but not display any value.

Paul

Re: How to convert date format DD-MM-YYYY into Year ?

Anil_Babu_Samineni — Wed, 17 Jan 2018 09:05:37 GMT

Image for field, Please? Don't have access to check

Re: How to convert date format DD-MM-YYYY into Year ?

surendraj — Wed, 17 Jan 2018 09:06:56 GMT

Try

Date(Date#([Expected Close Date],'DD/MM/YYYY'),'YYYY')

Re: How to convert date format DD-MM-YYYY into Year ?

paulyeo11 — Wed, 17 Jan 2018 09:08:01 GMT

Re: How to convert date format DD-MM-YYYY into Year ?

paulyeo11 — Wed, 17 Jan 2018 09:09:54 GMT

Hi Surendra,

Thank you for your help.

Your no error , not display any value.

Paul Yeo

Re: How to convert date format DD-MM-YYYY into Year ?

Anil_Babu_Samineni — Wed, 17 Jan 2018 09:10:28 GMT

Your format is MM-DD-YYYY, Right? Perhaps this?

Year(Date#([Expected Close Date], 'MM-DD-YYYY')) as Year

Re: How to convert date format DD-MM-YYYY into Year ?

surendraj — Wed, 17 Jan 2018 09:11:00 GMT

Where You are Trying?

In script? or anything else.!!

Re: How to convert date format DD-MM-YYYY into Year ?

paulyeo11 — Wed, 17 Jan 2018 09:13:26 GMT

wow you are very good.

Re: How to convert date format DD-MM-YYYY into Year ?

paulyeo11 — Wed, 17 Jan 2018 09:14:48 GMT

Hi Surendra

I using the attach QVW file to try your script. Any way now it is okay Anil already give me solution. Thank you

Paul

Re: How to convert date format DD-MM-YYYY into Year ?

sannidhikumar — Wed, 17 Jan 2018 09:14:57 GMT

hi Yeo Poh,

Try this in scripting.

Date(Date#([Expected Close Date], 'MM-DD-YYYY')) as Year or

Year(Date#([Expected Close Date], 'MM-DD-YYYY')) as Year

Re: How to convert date format DD-MM-YYYY into Year ?

surendraj — Wed, 17 Jan 2018 09:16:24 GMT

Cool..

Re: How to convert date format DD-MM-YYYY into Year ?

Anil_Babu_Samineni — Wed, 17 Jan 2018 09:18:13 GMT

Great, This case Avinash solution works as expected, Some changes with spell mistake

SubField([Expected Close Date],'-' ,3) as Year

Re: How to convert date format DD-MM-YYYY into Year ?

akshaye_c_navale — Wed, 17 Jan 2018 09:23:03 GMT

Hi,

Try below,

In load script used below code,

YEAR(REPLACE([Expected Close Date],'-','/')) AS [Expected Close Date]

Re: How to convert date format DD-MM-YYYY into Year ?

paulyeo11 — Wed, 17 Jan 2018 09:23:57 GMT

Hi Anil

You are good , you try every approach. Yes Avinash script is better , for those missing value it return as 0.

Paul

Re: How to convert date format DD-MM-YYYY into Year ?

paulyeo11 — Wed, 17 Jan 2018 09:26:09 GMT

Hi Akshay

your also working. but for those missing value , it does not return any value.

Thank you

Paul

Re: How to convert date format DD-MM-YYYY into Year ?

paulyeo11 — Wed, 17 Jan 2018 13:33:08 GMT

Hi Anil,

Can you please give me Month field script.

num(month([Expected Close Date])) as [month],

I assume the script should be like able , but it does not work.

Paul Yeo