topic Re: Date Conversion in QlikView

Date Conversion

Mon, 22 Apr 2013 16:06:14 GMT

I have a date field called "Filed_Date" in my Select from

In a Table Box Sheet Object the data looks like this: 3/26/2013 12:00:00 AM

I want to add another field in the data to select from which would look like Mar-2013.

I know in old SQL it would have been Select to_date (Filed_date, 'MMM-YYYY') as "Filed Month"

That is not working for me in QlikView.

Please help.

Thank you,

David Redden

Re: Date Conversion

Gysbert_Wassenaar — Mon, 22 Apr 2013 16:11:06 GMT

Well, to_date is an Oracle function. You can try: date(Filed_Date,'MMM-YYYY') as "Filed Month" if Filed_Date is already a date field. If it's a string try:

MonthName(date#(Filed_Date, 'M/D/YYYY hh:mm:ss TT'))

Re: Date Conversion

Mon, 22 Apr 2013 16:15:23 GMT

I have tried that and when I reload the data I get the following error:

ErrorSource: Microsoft OLE DB Provider for ODBC Drivers, ErrorMsg: [Oracle][ODBC][Ora]ORA-00936: missing expression

When I remove the item in bold below, it runs fine.( date(FILED_ON,'MMM-YYYY') AS "Filed Month",)

SELECT "INCIDENT_NUMBER" as "Incident #",

"CASE_NUMBER" as "Case #",

"PART_SEQUENCE" as "Part Seq",

"INCIDENT_NUMBER"||'-'||"CASE_NUMBER"||'-'||"PART_SEQUENCE" AS "Incident ID",

"INCIDENT_NUMBER"||'-'||"CASE_NUMBER"||'-'||"PART_SEQUENCE"||'-'||"REPORT_I_F_NO" AS "Source System Ref",

"FILED_ON" as "Filed Date",

date(FILED_ON,'MMM-YYYY') AS "Filed Month",

"FILED_BY" as "Filed By",

"MFR_REPORT_NO" as "MFR Rpt #",

"FOLLOWUP_REQUIRED" as "F/U Reqd"

FROM CHSUSER."REGULATORY_FILINGS"

WHERE "FILED_ON" >= SYSDATE -30

AND "REPORT_TYPE" = 'MDR';

Re: Date Conversion

Gysbert_Wassenaar — Mon, 22 Apr 2013 16:45:28 GMT

Ah yeah, feeding an oracle database qlikview functions is not going to make it happy. Qlikview sends a select statement as is to the database. It doesn't parse any of it itself. If you want to use Qlikview functions you need to do it in a load statement. You could for example you a preceding load to process the results returned by the sql statement:

load *, date([Filed Date],'MMM-YYYY') AS "Filed Month";

SELECT "INCIDENT_NUMBER" as "Incident #",

"CASE_NUMBER" as "Case #",

"PART_SEQUENCE" as "Part Seq",

"INCIDENT_NUMBER"||'-'||"CASE_NUMBER"||'-'||"PART_SEQUENCE" AS "Incident ID",

"INCIDENT_NUMBER"||'-'||"CASE_NUMBER"||'-'||"PART_SEQUENCE"||'-'||"REPORT_I_F_NO" AS "Source System Ref",

"FILED_ON" as "Filed Date",

-- to_char(FILED_ON,'MMM-YYYY') as Filed_Month

"FILED_BY" as "Filed By",

"MFR_REPORT_NO" as "MFR Rpt #",

"FOLLOWUP_REQUIRED" as "F/U Reqd"

FROM CHSUSER."REGULATORY_FILINGS"

WHERE "FILED_ON" >= SYSDATE -30

AND "REPORT_TYPE" = 'MDR';

In oracle sql you can use the to_char function to format a date as a string.

Re: Date Conversion

Tue, 23 Apr 2013 18:41:42 GMT

I figured it out.

It was Load *, MonthName("Filed Date") as "Filed Month";

OLEDB CONNECT TO....

SQL SELECT....

But it was because of your response that got me there...>Thank you so much!!

Loving Qlikview!!!