FORMATING DATE - HELP

[#1]

Hi

I'm new to qlikview, having used this over a few days I am getting slightly frustrating with the script language. I am trying to format the month to return a Text rather than a number.

With my script below, it returns a number, not text. Can someone help????

SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DateFormat='DD/MM/YYYY';
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`,
Year(se_DATEIMPORT)as TYEAR,
Month (se_DATEIMPORT)as TMONTH

FROM `dbo_SwapExposure`;

[#2]

Month() returns the numeric month of the date. MonthName() should return the month abbreviation plus the year.

Left(MonthName(se_DATEIMPORT), 3) as TMONTH should give you just the month abbreviation.

[#3]

Hi

Thanks for getting back to me

Left(MonthName(se_DATEIMPORT),3) as TMONTH

I entered this in and I get an error

[#4]

What is the error? What is the format of se_DATEIMPORT?

Try entering this:

Left(MonthName(Now()), 3) As TodayMonth

I did that in a test and it worked. It could be that it is not recognizing se_DATEIMPORT as a Date. You may have to use Date() or Date#() to convert it first.

[#5]

Hi

This is the format of the date field = 22/09/2010

Im new to script writing on qlikview

How would I edit this query so it returns the abbreivation name of the month rather than number

SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DateFormat='DD/MM/YYYY';
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`,
Year(se_DATEIMPORT)as TYEAR,
Left(MonthName(se_DATEIMPORT),3) as TMONTH

FROM `dbo_SwapExposure`;

[#6]

Hi,

You can use following script :

Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH

If this doesn't work, remove # after Date in above script.

Still if it gives error, Plz share sample data if you can.

Happy Thoughts

[#7]

Hi

Still no luck. I must be doing something wrong. This is what I am trying to run now

SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DateFormat='DD/MM/YYYY';
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`,
Year(se_DATEIMPORT)as TYEAR,
Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH

FROM `dbo_SwapExposure`;

[#8]

This is my error:

SQL Error:[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3)'.
SQL Scriptline:
SQL State:37000
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`,
Year(se_DATEIMPORT)as TYEAR,
Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH

FROM `dbo_SwapExposure`

[#9]

Hi

MonthName is a Qlikview function, not SQL

you have to load first in a table T1 from SQL query and then you can use T2 : LOad *, left(monthname(....) as Tmonth resident T1

you can make it in one load using

Load *, left(monthname(....) as Tmonth;

SQL ....FROM dbo_SwapExposure

regards

christian

[#10]

Hi

This is what I wrote and tried to load butit didn't work

ODBC CONNECT TO [MS Access Database;DBQ=G:\ETP_TRADING AND OPERATIONS\Daily internal reports\Reporting DB\IM Access Database\ASSENAGON.mdb];
LOAD `se_ID`,
`se_FUND`,
`se_AP`,
`se_SwapNotional`,
`se_FundNAV`,
`se_SwapMTM`,
`se_SwapMTMNotionalPer`,
`se_SwapMTMFundNAVPer`,
Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH,
`se_IMPORTID`;

SQL SELECT `se_ID`,
`se_FUND`,
`se_AP`,
`se_SwapNotional`,
`se_FundNAV`,
`se_SwapMTM`,
`se_SwapMTMNotionalPer`,
`se_SwapMTMFundNAVPer`,
`se_DATEIMPORT`,
`se_IMPORTID`
Year(TMONTH)as YEAR,
MONTH(TMONTH)as REPORTING_MONTH
FROM `dbo_SwapExposure`;