topic Date Substringing in QlikView

Date Substringing

Wed, 20 Apr 2011 17:38:31 GMT

Need to substring out Month and Year from a date field. The field format is DD/MM/YYYY

I am currently Using :

Month("RFPForce__Due_Date__c") AS RFPForce_DueDate_MONTH,
Year("RFPForce__Due_Date__c") AS RFPForce_DueDate_YEAR,

The Problem I have is the Month substring returns a numeric value for the month (1 for January).. Is there a way to formulariclly have the month represented as either the Month Name or a month abbreviation?

Thanx

Date Substringing

disqr_rm — Wed, 20 Apr 2011 17:42:34 GMT

Just change the filed format to default on your sheet and it should show you the month names correctly. It seems you have the field formatted as number.

Date Substringing

erichshiino — Wed, 20 Apr 2011 17:43:30 GMT

Try:

Text( date(RFPForce__Due_Date__c,'MMM') )

( date(RFPForce__Due_Date__c,'MMM') )

or Text( date(RFPForce__Due_Date__c,'MMMM') )

Date Substringing

Wed, 20 Apr 2011 17:46:01 GMT

Hi, you can modify this example:

MONTHS_NAME:
Mapping Load * Inline [
Month, MonthName
1,'Jan'
2,'Fev'
3,'Mar'
4,'Abr'
5,'Mai'
6,'Jun'
7,'Jul'
8,'Ago'
9,'Set'
10,'Out'
11,'Nov'
12,'Dez'];

TEST:
Load
applymap('MONTHS_NAME',Month(RFPForce__Due_Date__c)) as MONTH_NAME,
Day(RFPForce__Due_Date__c) as Day
RESIDENT DATES

Date Substringing

Wed, 20 Apr 2011 17:48:00 GMT

Please forgive my lack of training, but how do you do that?

Date Substringing

Wed, 20 Apr 2011 18:01:42 GMT

These are the default settings I am using.....what needs to change?

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';