topic Re: conversion of date in QlikView

conversion of date

upaliwije — Thu, 15 Jan 2015 15:40:45 GMT

In my oracle table the date is stored in text format like 01-JAN-2015 (paid_date)

I want to link this Paid_date to master calendar. How can I do it? should I convert the paid_date to date format? if so pls help me

Re: conversion of date

rubenmarin — Thu, 15 Jan 2015 15:50:00 GMT

Hi upali, you can do that or use a replace() or applymap() function to 'translate' month to numbers, ie:

Replace(paid_date, 'JAN', '01','FEB','02'.....)

For applymap it can be:

Map_Months:

Mapping LOAD * Inline [

Month, Number

JAN, 1

FEB, 2

];

Data:

LOAD ...

MakeDate(Right(paid_date, 4), ApplyMap('Map_Months', Mid(paid_date, 4, 3)), Left(paid_date, 2)) as paid_date,

...

Instead of Right, Mid and Left functions you can also use Subfield(paid_date, '-', Number1-3)

Re: conversion of date

Anonymous — Thu, 15 Jan 2015 15:50:29 GMT

date ( date#('01-JAN-2015' , 'DD-MMM-YYYY' ) , 'DD MMM YYYY' )

Re: conversion of date

MarcoWedel — Thu, 15 Jan 2015 23:53:43 GMT

Hi,

Date#() is the function you are looking for, as already proposed by bill.markham (like in your thread Character Field convert to date format by maxgro and jagan)

LOAD Date#(paid_date, 'DD-MMM-YYYY') as paid_date
FROM yourSource;

will load the paid_date field as date, if your MonthNames are set accordingly

(SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';).

hope this helps

regards

Marco

Re: conversion of date

kavita25 — Fri, 16 Jan 2015 07:33:17 GMT

Hi,

Check the given application

Re: conversion of date

Colin-Albert — Fri, 16 Jan 2015 07:52:29 GMT

Date# converts text to dates. Date#(DD-MMM-YYYY)

Full details can be found in these blogs

The Date Function

Get the Dates Right

Re: conversion of date

maxgro — Fri, 16 Jan 2015 07:59:10 GMT

you should look at the data type of your oracle column

if it's a VARCHAR2, you need date# in QlikView

if it's a DATE usually you don't need any interpretation function in QlikView

Re: conversion of date

engishfaque — Fri, 16 Jan 2015 07:59:45 GMT

Dear Upali,

Use this script in Load,

Date(YourDateFieldName, 'DD-MMM-YYYY') as Paid_Date

Kind regards,

Ishfaque Ahmed

Re: conversion of date

upaliwije — Fri, 16 Jan 2015 11:01:18 GMT

Thanks all