topic Re: Extract Date from String in QlikView

Extract Date from String

vardhancse — Wed, 06 Sep 2017 09:51:52 GMT

Hi,

Have one requirement is that, Have 2 string columns

Need to derive date (DD/MM/YYYY hh:mm) format from those 2 columns .

Individually I tried to extract using Subfield and concatenate but the issue is with month I need to convert to number like for e.g Jan to 01 and similarly for all months

PFA sample app for reference.

Re: Extract Date from String

kaushiknsolanki — Wed, 06 Sep 2017 09:57:36 GMT

Try this.

Sample:

LOAD *,Date(Date#(Left([Start Date],11),'MMM/DD/YYYY')) as SD,Date(Date#(Left([End Date],11),'MMM/DD/YYYY')) as ED INLINE [

Start Date, End Date

Sep/26/2014 01:07:39 AM IST, Sep/27/2014 01:07:39 AM IST

Dec/10/2014 04:35:45 AM IST, Dec/11/2014 04:35:45 AM IST

Jan/13/2015 02:35:37 AM IST, Jan/14/2015 02:35:37 AM IST

];

Regards,

Kaushik Solanki

Re: Extract Date from String

its_anandrjs — Wed, 06 Sep 2017 10:08:27 GMT

Try this with subfield and Date

Sample:
LOAD *,
Date(Date#([Start Date],'MMM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm') as [Start Date New],
Date(Date#([End Date],'MMM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm') as [End Date New];

LOAD
Mid([Start Date],1,Len([Start Date])-3) as [Start Date],
Mid([End Date],1,Len([End Date])-3) as [End Date];
LOAD * INLINE [
Start Date, End Date
Sep/26/2014 01:07:39 AM IST, Sep/27/2014 01:07:39 AM IST
Dec/10/2014 04:35:45 AM IST, Dec/11/2014 04:35:45 AM IST
Jan/13/2015 02:35:37 AM IST, Jan/14/2015 02:35:37 AM IST
];

See attached also

Re: Extract Date from String

vardhancse — Wed, 06 Sep 2017 10:10:19 GMT

Hi Kaushik,

thank you.

I need to have time stamp as well, because I need to calculate ED-CD and so.

Could you please let me know how to include time stamp as well

Re: Extract Date from String

vardhancse — Wed, 06 Sep 2017 10:17:45 GMT

Hi Anand,

Thank you.

My requirement is to convert month like Dec to 12.

Can please let me know how to do the same

Input: Dec/10/2014 04:35:45 AM IST

Expected output: 10/12/2014 04:35

Re: Extract Date from String

kaushiknsolanki — Wed, 06 Sep 2017 10:21:51 GMT

Sample:

LOAD *,Timestamp(timestamp#(Left([Start Date],23),'MMM/DD/YYYY hh:mm:ss TT')) as SD,Timestamp(timestamp#(Left([End Date],23),'MMM/DD/YYYY hh:mm:ss TT')) as ED INLINE [

Start Date, End Date

Sep/26/2014 01:07:39 AM IST, Sep/27/2014 01:07:39 AM IST

Dec/10/2014 04:35:45 AM IST, Dec/11/2014 04:35:45 AM IST

Jan/13/2015 02:35:37 AM IST, Jan/14/2015 02:35:37 AM IST

];

Regards,

Kaushik Solanki

Re: Extract Date from String

its_anandrjs — Wed, 06 Sep 2017 10:27:18 GMT

On my above solution it is converted in your expected format kind check

OutPut

See attached here

Re: Extract Date from String

Kushal_Chawda — Wed, 06 Sep 2017 10:56:41 GMT

try this

timestamp(Timestamp#(purgechar([Start Date],'IST'),'MMM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm') as [Start Date],

Re: Extract Date from String

its_anandrjs — Wed, 06 Sep 2017 11:09:58 GMT

Kushal purgechar will remove the S from Sep

Re: Extract Date from String

Kushal_Chawda — Wed, 06 Sep 2017 11:16:56 GMT

correct , we can try below then

=timestamp(Timestamp#(mid([Start Date],1,Index([Start Date],'IST')-2),'MMM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm') as [Start Date]

Re: Extract Date from String

its_anandrjs — Wed, 06 Sep 2017 11:22:25 GMT

This is also worked

Mid([Start Date],1,Len([Start Date])-3) as [Start Date]

Mid([Start Date],1,Len([Start Date])-4) as [Start Date]

Re: Extract Date from String

vardhancse — Thu, 07 Sep 2017 07:39:54 GMT

Fine thanks.

Could you please let me know how to convert to below format:

Output: DDMMYYYY

Re: Extract Date from String

vardhancse — Thu, 07 Sep 2017 07:40:23 GMT

Thanks could you please let me know how to converted to only DDMMYY.

Re: Extract Date from String

Kushal_Chawda — Thu, 07 Sep 2017 07:44:07 GMT

try this

date(floor(Timestamp#(mid([Start Date],1,Index([Start Date],'IST')-2),'MMM/DD/YYYY hh:mm:ss TT')),'DDMMYY') as [Start Date]

Re: Extract Date from String

its_anandrjs — Thu, 07 Sep 2017 08:05:39 GMT

This ways

Date(Date#([Start Date],'MMM/DD/YYYY hh:mm:ss TT'),'DDMMYYYY') as [Start Date New]

Date(Timestamp#([Start Date],'MMM/DD/YYYY hh:mm:ss TT'),'DDMMYYYY') as [Start Date New]