Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

To extract date from String

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

S

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

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

View solution in original post

4 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

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

sureshbaabu
Creator III
Creator III

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,

Not applicable
Author

hi

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

Anonymous
Not applicable
Author

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

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