Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
shashank20
New Contributor III

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

Tags (2)
1 Solution

Accepted Solutions
shashank20
New Contributor III

Re: To extract date from String

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

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

4 Replies

Re: To extract date from String

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
Contributor III

Re: To extract date from String

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

Re: To extract date from String

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

shashank20
New Contributor III

Re: To extract date from String

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

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

Community Browser