Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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
date ( date#('01-JAN-2015' , 'DD-MMM-YYYY' ) , 'DD MMM YYYY' )
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)
date ( date#('01-JAN-2015' , 'DD-MMM-YYYY' ) , 'DD MMM YYYY' )
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
Hi,
Check the given application
Date# converts text to dates. Date#(DD-MMM-YYYY)
Full details can be found in these blogs
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
Dear Upali,
Use this script in Load,
Date(YourDateFieldName, 'DD-MMM-YYYY') as Paid_Date
Kind regards,
Ishfaque Ahmed
Thanks all