topic Re: To extract date from String in QlikView

To extract date from String

Anonymous — Tue, 04 Feb 2014 02:31:39 GMT

Hi Geniuses,

I Have a Contractual year in string form

1 Apr 2012 to 31 Mar 2014

1 Dec 2013 to 30 Nov 2014

1 Jan 2011 to 31 Dec 2011

1 Jan 2011 to 31 Dec 2015

1 Jan 2012 to 31 Dec 2012

1 Jan 2012 to 31 Dec 2013

1 Jan 2013 to 31 Dec 2013

1 Jan 2013 to 31 Dec 2014

1 Jan 2014 to 31 Dec 2014

1 Jan 2015 to 31 Dec 2015

1 Jul 2012 to 30 Jun 2014

1 Jul 2013 to 30 Jun 2014

1 Nov 2013 to 31 Oct 2014

1 Nov 2013 to 31 Oct 2015

1 Oct 2013 to 30 Sep 2014

1 Sep 2012 to 31 Aug 2014

15 Apr 2013 to 14 Apr 2014

Now I want the start and the end date from these Contractual year

so I want something like this

Contractual Year Start

1/01/2011

1/01/2012

1/04/2012

1/07/2012

1/09/2012

1/01/2013

15/04/2013

1/07/2013

1/10/2013

1/11/2013

1/12/2013

1/01/2014

1/01/2015

Contractual Year end

31/12/2011

31/12/2012

31/12/2013

31/03/2014

14/04/2014

30/06/2014

31/08/2014

30/09/2014

31/10/2014

30/11/2014

31/12/2014

31/10/2015

31/12/2015

So now you see i f I select Contractual year as

1 Apr 2012 to 31 Mar 2014 my Contractual start will be 01/01/2012 and Contractual end will be 31/03/2014

Thanks in advance

Re: To extract date from String

CELAMBARASAN — Tue, 04 Feb 2014 03:29:51 GMT

Derive the fields from script itself like

Date(Date#(SubField(ContractualYear, ' to ', 1), 'D MMM YYYY'), 'DD/MM/YYYY') AS ContractualStart

Date(Date#(SubField(ContractualYear, ' to ', 2), 'D MMM YYYY'), 'DD/MM/YYYY') AS ContractualEnd

It will create two fields as expected

Thanks

Celambarasan

Re: To extract date from String

sureshbaabu — Tue, 04 Feb 2014 03:49:39 GMT

Please convert your string to date using the expressions below on your load script

Please go with SubField function as suggested above. But, you can make use of Left and right functions if the source date format is different.

date(Date#(left(datecolumn,11),'DD MMM YYYY'),'DD/MM/YYYY') as start_date,

date(Date#(Right(datecolumn,11),'DD MMM YYYY'),'DD/MM/YYYY') as End_date

Hope it helps!!

Thanks,

Re: To extract date from String

Tue, 04 Feb 2014 03:59:02 GMT

for start date--

=Date(Date#(left('1 Apr 2012 to 31 Mar 2014',11),'DD MMM YYYY'),'DD/MM/YYYY')

for end date--

=Date(Date#(Right('1 Apr 2012 to 31 Mar 2014',11),'DD MMM YYYY'),'DD/MM/YYYY')

********************************************************************************************************

in script write this

Date(Date#(left(Contractualdate,11),'DD MMM YYYY'),'DD/MM/YYYY') as startdate

Date(Date#(Right(Contractualdate,11),'DD MMM YYYY'),'DD/MM/YYYY') as enddate

Re: To extract date from String

Anonymous — Tue, 04 Feb 2014 06:58:34 GMT

I have did that by using PurgeChar , Subfield and the the Date#

Thanks alot guys ...........